How to do a restore of a large postgresql 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.