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 &amp;&amp; cd x
tar -xzvf ../20200204_data_tar.gz


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


&ldquo;plain&rdquo; 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 &lt;SQL-file&gt; -h &lt;hostname&gt; -p &lt;port&gt; -U &lt;user&gt; -d &lt;target-database&gt;

&ldquo;custom&rdquo; 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 &lt;hostname&gt; -p &lt;port&gt; -U &lt;user&gt; -d &lt;target-database&gt; &lt;dump-file&gt;

&ldquo;directory&rdquo; 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 &lt;hostname&gt; -p &lt;port&gt; -U &lt;user&gt; -d &lt;target-database&gt; &lt;directory-containing-toc.dat&gt;

&ldquo;tar&rdquo; 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.