Compute
Elastic Cloud Server
Huawei Cloud Flexus
Bare Metal Server
Auto Scaling
Image Management Service
Dedicated Host
FunctionGraph
Cloud Phone Host
Huawei Cloud EulerOS
Networking
Virtual Private Cloud
Elastic IP
Elastic Load Balance
NAT Gateway
Direct Connect
Virtual Private Network
VPC Endpoint
Cloud Connect
Enterprise Router
Enterprise Switch
Global Accelerator
Management & Governance
Cloud Eye
Identity and Access Management
Cloud Trace Service
Resource Formation Service
Tag Management Service
Log Tank Service
Config
OneAccess
Resource Access Manager
Simple Message Notification
Application Performance Management
Application Operations Management
Organizations
Optimization Advisor
IAM Identity Center
Cloud Operations Center
Resource Governance Center
Migration
Server Migration Service
Object Storage Migration Service
Cloud Data Migration
Migration Center
Cloud Ecosystem
KooGallery
Partner Center
User Support
My Account
Billing Center
Cost Center
Resource Center
Enterprise Management
Service Tickets
HUAWEI CLOUD (International) FAQs
ICP Filing
Support Plans
My Credentials
Customer Operation Capabilities
Partner Support Plans
Professional Services
Analytics
MapReduce Service
Data Lake Insight
CloudTable Service
Cloud Search Service
Data Lake Visualization
Data Ingestion Service
GaussDB(DWS)
DataArts Studio
Data Lake Factory
DataArts Lake Formation
IoT
IoT Device Access
Others
Product Pricing Details
System Permissions
Console Quick Start
Common FAQs
Instructions for Associating with a HUAWEI CLOUD Partner
Message Center
Security & Compliance
Security Technologies and Applications
Web Application Firewall
Host Security Service
Cloud Firewall
SecMaster
Anti-DDoS Service
Data Encryption Workshop
Database Security Service
Cloud Bastion Host
Data Security Center
Cloud Certificate Manager
Edge Security
Situation Awareness
Managed Threat Detection
Blockchain
Blockchain Service
Web3 Node Engine Service
Media Services
Media Processing Center
Video On Demand
Live
SparkRTC
MetaStudio
Storage
Object Storage Service
Elastic Volume Service
Cloud Backup and Recovery
Storage Disaster Recovery Service
Scalable File Service Turbo
Scalable File Service
Volume Backup Service
Cloud Server Backup Service
Data Express Service
Dedicated Distributed Storage Service
Containers
Cloud Container Engine
SoftWare Repository for Container
Application Service Mesh
Ubiquitous Cloud Native Service
Cloud Container Instance
Databases
Relational Database Service
Document Database Service
Data Admin Service
Data Replication Service
GeminiDB
GaussDB
Distributed Database Middleware
Database and Application Migration UGO
TaurusDB
Middleware
Distributed Cache Service
API Gateway
Distributed Message Service for Kafka
Distributed Message Service for RabbitMQ
Distributed Message Service for RocketMQ
Cloud Service Engine
Multi-Site High Availability Service
EventGrid
Dedicated Cloud
Dedicated Computing Cluster
Business Applications
Workspace
ROMA Connect
Message & SMS
Domain Name Service
Edge Data Center Management
Meeting
AI
Face Recognition Service
Graph Engine Service
Content Moderation
Image Recognition
Optical Character Recognition
ModelArts
ImageSearch
Conversational Bot Service
Speech Interaction Service
Huawei HiLens
Video Intelligent Analysis Service
Developer Tools
SDK Developer Guide
API Request Signing Guide
Terraform
Koo Command Line Interface
Content Delivery & Edge Computing
Content Delivery Network
Intelligent EdgeFabric
CloudPond
Intelligent EdgeCloud
Solutions
SAP Cloud
High Performance Computing
Developer Services
ServiceStage
CodeArts
CodeArts PerfTest
CodeArts Req
CodeArts Pipeline
CodeArts Build
CodeArts Deploy
CodeArts Artifact
CodeArts TestPlan
CodeArts Check
CodeArts Repo
Cloud Application Engine
MacroVerse aPaaS
KooMessage
KooPhone
KooDrive
Help Center/ Relational Database Service/ User Guide/ Working with RDS for PostgreSQL/ Data Restorations/ Restoring Data to an On-Premises PostgreSQL Database from a Full Backup

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

Updated on 2024-10-14 GMT+08:00

This section describes how to restore an RDS for PostgreSQL instance to an on-premises PostgreSQL database from a full backup.

If you want to migrate all data from your RDS for PostgreSQL DB instance to an on-premises PostgreSQL database, you can download a full backup in .tar.gz format and restore it to the on-premises database following the steps described in this section.

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 a Full Backup File.
  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.
    NOTICE:

    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:
    NOTE:

    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

    NOTE:

    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;

We use cookies to improve our site and your experience. By continuing to browse our site you accept our cookie policy. Find out more

Feedback

Feedback

Feedback

0/500

Selected Content

Submit selected content with the feedback