How to do a restore of a large postgresql database?

Question

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?

• Laurenz Albe: Can you specify what you mean by "didn't work"?
• user6826691: I have added more details to my question
• user1822: 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.

Answer 1

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.

Answer 2

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