CODEWITHSUNDEEP
pg-dumppg-restorepostgresql-14
Santhosh Kumar
Santhosh Kumar

Reputation: 53

Postgresql restore with compressed dump file

I took a postgresql DB Bacup with the below command

pg_dump -Z6 -h localhost test_db -f test_db.tar -p port

Now If I try to restore it with psql facinf the below error:

psql:test_db.sql:117359: error: invalid command \gg<CB>t<E4>Iƣ<AC>=<AF>*ESC^TDuV^P^Ov<B6><B0>
psql:test_db.sql:117362: error: invalid command \a>@
psql:test_db.sql:117363: error: invalid command \"<99>k<86>
psql:test_db.sql:117372: error: invalid command \<9B>遥<FD><FE><BA>j
psql:test_db.sql:117406: ERROR:  invalid byte sequence for encoding "UTF8": 0x8a

With pg_restore:

pg_restore: error: input file does not appear to be a valid archive

I thought i was using -Fc with pg_dump, but somehow I missed it. Now Can you help me to restore this Backup to postgresql.

I'm Using PostgreSQL 14.5

I've tried below commands

  1. cat test_db.tar | psql test_db -U postgres -h localhost -p port

  2. \i testdb.tar

  3. even Tried to rename the dump file from test_db.tar to test_db.dump and tried restoring it. Nothing is working.

Upvotes: 3

Views: 4346

Answers (2)

Santhosh Kumar
Santhosh Kumar

Reputation: 53

Since I didn't mention the format of the dump with -F, by default it created a plain dump file with -Z6 compression and with the extension of .tar

The mistake on my end was that I forgot to mention the dump file format.

After trying the below command, it worked

zcat test_db.tar | psql -d test_db -U postgres -h localhost -p port

Upvotes: 1

user330315
user330315

Reputation:

It seems -Z6 generates a gzipped file.

You can convert that into a plain text SQL script that psql can read using. To unzip it using gzip you will need to rename it, e.g. to test_db.sql.gz

Then you can use:

gzip -k -d test_db.sql.gz

-k will keep the original file

Upvotes: 1

Related Questions