Restoring Data to an On-Premises PostgreSQL Database from a Full Backup

Operation Process

1. Download the target full backup of your RDS for PostgreSQL DB instance.
2. Upload the full backup to the on-premises PostgreSQL database.
3. Use the tar utility to decompress the full backup.
4. Store the configuration files of the on-premises database in a temporary directory and run the OS commands to copy the extracted full backup files to the data directory of the on-premises database.
5. Restart the database and wait until the database restoration is complete.

Constraints

• This section does not apply to restoration using incremental backups.
• The minor version of the on-premises PostgreSQL database must be the same as that of your RDS for PostgreSQL DB instance.

  To view the PostgreSQL kernel version, run psql -V or psql --version.

• The backup can be used to restore only to an on-premises database running Linux. The tar utility must be installed on the OS.

  To install this tool, run sudo yum install tar.

• During the restoration, do not run any other services on or store service data to the on-premises database.
• RDS for PostgreSQL has certain enhanced features, such as failover slots, which may cause SQL errors on the restored on-premises database. You need to delete such enhanced features from the on-premises database. For details, see FAQ.
• The OS of the on-premises database may be different from that of RDS. The sorting rules of some PostgreSQL indexes are subject to the OS. After data is restored to the on-premises database, you need to rebuild the indexes. To find out which indexes need to be rebuilt, see Locale data changes.

Step 1: Download a Full Backup of Your RDS for PostgreSQL DB Instance

RDS for PostgreSQL DB instances automatically perform a full backup at a scheduled time you specified. You can also create manual backups. When a full backup is created, download the generated .tar.gz file.

1. On the Instances page, click the instance name. On the displayed page, choose Backups & Restorations > Full Backups and click Download. For details, see Downloading an Instance-Level Backup.
2. Use a file transfer tool (such as WinSCP) to upload the full backup file to the Linux device where the on-premises PostgreSQL database is running.

Step 2: Restore to the On-Premises PostgreSQL Database from the Backup

Notes

Modify certain information based on your site requirements:

1. Store the RDS for PostgreSQL backup file in different directories before and after decompression.
   • Before: /home/postgres/Full backup.tar.gz
   • After: /home/postgres/backuprds
2. Store the postgresql.conf and pg_hba.conf configuration files in the data directory of the on-premises PostgreSQL database to the /home/postgres/backuplocal directory.
3. Use the postgres user to install the on-premises PostgreSQL database.
4. Replace $PGDATA in the commands described below with the data directory of your on-premises PostgreSQL database. To query the data directory, run the following commands:

   su - postgres

   psql --host=localhost --port=<DB_PORT> --dbname=postgres --username=postgres -c "show data_directory;"

   DB_PORT indicates the port number of the on-premises database. The default value is 5432.

Procedure

1. Switch to the postgres user and create a temporary directory backuprds. Perform all the following steps as postgres.

   su - postgres

   mkdir /home/postgres/backuprds

2. Stop the on-premises PostgreSQL database.

   pg_ctl stop -D $PGDATA

3. Create a temporary directory to store the postgresql.conf and pg_hba.conf configuration files in the data directory of the on-premises PostgreSQL database.

   mkdir /home/postgres/backuplocal

   cp $PGDATA/pg_hba.conf $PGDATA/postgresql.conf /home/postgres/backuplocal

4. Clear the data directory of the on-premises database.
   

   Before performing this operation, ensure that the data in the $PGDATA/ directory is no longer needed.

   To view files in the $PGDATA/ directory, run the ls -l $PGDATA command.

   rm -rf $PGDATA/*

5. Run the following command to decompress the backup to the directory prepared in 1:
   

   If you upload the backup file to /home/postgres/Full backup.tar.gz as user root, you need to change the owner of the file.

   1. Run the sudo su command to switch to user root.
   2. Run the chown -R postgres:postgres /home/postgres/Full backup.tar.gz command to change the file owner to the postgres user.
   3. Run the su - postgres command to switch back to postgres.

   tar -zxf /home/postgres/Full backup.tar.gz -C /home/postgres/backuprds

   After the decompression, the following folders are generated in /home/postgres/backuprds:

   • base: stores full backup files.
   • pg_wal: stores incremental backup files. For PostgreSQL 9.x, the generated directory is pg_xlog.
   • Tablespace folders named using digits (if the original backup contains tablespace files)
6. Copy the files in 5 and 3 to the specified directories of the on-premises database in sequence.
   1. Copy all files in the base directory to the data directory, and then replace the two files in the data directory with the configuration files in 3.

      cp -r /home/postgres/backuprds/base/* $PGDATA

      cp -r /home/postgres/backuplocal/* $PGDATA

   2. Copy the files in the pg_wal directory (or the pg_xlog directory for PostgreSQL 9.x) to pg_wal (or pg_xlog for PostgreSQL 9.x) under the data directory of the on-premises database.

      cp -r /home/postgres/backuprds/pg_wal/* $PGDATA/pg_wal

   3. (Optional) If there are tablespace files in the original backup, modify the tablespace soft link information in the data/tablespace_map file.
      • Copy the tablespace files to the /tmp/tblspc/ directory.

        If the decompressed file contains multiple tablespace directories, run the cp -r /home/postgres/backuprds/$table_space /tmp/tblspc command repeatedly to ensure that all tablespaces are copied to the /tmp/tblspc directory.

        mkdir /tmp/tblspc

        cp -r /home/postgres/backuprds/$table_space /tmp/tblspc

        $table_space indicates the name of the tablespace folder obtained in 5.

      • Delete the /tablespace_map file from the data directory of the on-premises database.

        rm -rf $PGDATA/tablespace_map

      • Add a new /tablespace_map file to the data directory of the on-premises database. If the decompressed file contains multiple tablespace directories, run the following command repeatedly to ensure that the soft link information of the tablespaces is complete:

        echo "$table_space /tmp/tblspc/$table_space" >> $PGDATA/tablespace_map

7. Restart the database and wait until the database restoration is complete.

   pg_ctl start -D $PGDATA

   

   If the cloud database is processing many write requests during the backup, there will be a large number of WAL logs in the pg_wal directory. It may take a long time to replay WAL logs when the database is started. As a result, the startup command may time out and fail.

   To check the restoration progress, run the ps uxwwf | grep 'startup' command.

FAQ

Data Restoration Issues

Q: How do I restore data if no backup is available?

A: You can migrate data using Data Replication Service (DRS). For details, see From PostgreSQL to PostgreSQL.

Backup and Restoration Issues

• Q1: After I restored data to an on-premises database using an RDS backup, the database failed to start and the error message "replication slot file xxx has corrupted length xxx" was displayed. What should I do?

  A: Delete all files and folders under the pg_replslot directory and then restart the database.

• Q2: What are the causes for the error "could not locate a valid checkpoint record"?

  A: This error usually indicates that the checkpoint record in the database is damaged or lost. As a result, the database cannot be restored. Generally, it is because WAL logs are not properly loaded. Handle the problem by referring to 6.b.

RDS for PostgreSQL 11 Data Restoration Issues

• Q1: What should I do if the error message "ERROR: internal function "int4_text" is not in internal lookup table" is displayed during the conversion from int4 to text when an RDS for PostgreSQL 11 instance is restored to a local PostgreSQL 11 database?

  A: Connect to the on-premises PostgreSQL 11 database as the installation user (such as postgres) and run the following command to delete the conversion rule:

  delete from pg_cast where castsource = 'int4'::regtype and casttarget = 'text':: regtype;

• Q2: What can I do if multiple type conversion functions generate errors when an RDS for PostgreSQL 11 instance is restored to a local PostgreSQL 11 database?

  A: Run the following SQL statement on the on-premises PostgreSQL 11 database and RDS for PostgreSQL 11 instance, respectively, and compare the results:

  select oid, * from pg_cast order by 1;

  For the new type conversion rules of RDS for PostgreSQL 11, run the following SQL statement on the local PostgreSQL 11 database to delete the rules:

  delete from pg_cast where castsource = xxx and casttarget = xxx;