1

I'm working on restoring a backup from on-premise postgresql to another postgresql database hosted in AWS (rds-aurora postgresql, serverless). The data size is 170gb of compressed data.

I earlier thought to do using pg_restore because I can pass the -j option, but it's taking a lot of time and space to extract the files.

My file is in the format = tar.gz (20200204_data_tar.gz)

I tried to extract and pass it to pg_restore using pipe to save time , but got the below error. Note : backup was generated using pg_start_backup.

tar -xzOf 20200204_data_tar.gz | pg_restore
    --host=my-test.us-east-1.rds.amazonaws.com --port=5432 --username=postgresql--dbname=mytest -j 16 --password --verbose --exit-on-error --data-only 

pg_restore: [archiver] input file does not appear to be a valid archive

On-premise version 9.3.4 New Cluster version is using 10.7

Can someone please suggest me options on how to do a restore for this large database?

3
  • Can you specify what you mean by "didn't work"? Feb 4, 2020 at 22:31
  • I have added more details to my question Feb 4, 2020 at 22:41
  • How exactly did you create the backup (20200204_data_tar.gz), please edit your question and add the complete command you used to create that file. Feb 5, 2020 at 8:41

2 Answers 2

4

Of course the best option would be to remember how exactly you took that backup...

If you want to restore the backup on a hosted database, it had better be a logical backup created with pg_dump. So make sure it is not a physical backup:

  • Run tar -tzvf 20200204_data_tar.gz and make sure it does not contain files like postgresql.auto.conf or PG_VERSION.

Once you have ascertained that, unpack the archive to a new directory and check what you have got:

mkdir x && cd x
tar -xzvf ../20200204_data_tar.gz

Then check out which dump format the file is and restore it accordingly:

  • “plain” format:

    The archive contains a single text file with SQL statements, and somebody was weird enough to pack a single file into a tar archive.

    Such a dump is restored with

    psql -f <SQL-file> -h <hostname> -p <port> -U <user> -d <target-database>
    
  • “custom” format:

    The archive contains a single binary file, and somebody was weird enough to pack a single file into a tar archive.

    Such a dump is restored with

    pg_restore -h <hostname> -p <port> -U <user> -d <target-database> <dump-file>
    
  • “directory” format:

    The archive contains a file toc.dat and many files of the form number.dat.gz.

    Such a dump is restored with

    pg_restore -h <hostname> -p <port> -U <user> -d <target-database> <directory-containing-toc.dat>
    
  • “tar” format:

    The archive contains the files toc.dat and restore.sql and many files of the form number.dat.

    Such a dump can be restored just like the directory format above.

0

You can try with below
1. For extract your file tar xvzf your_file.tar.gz
2. For restore psql -h localhost -d database_name -f database_file_location

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.