How to do a restore of a large postgresql database?

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?

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  -h  -p  -U  -d 

-
“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  -p  -U  -d  

-
“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  -p  -U  -d  

-
“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.

StackExchange Database Administrators Q#258910, answer score: 7