Thread: backup -restore question
Hello there,
One of our PostgreSQL 9.4.1 databases has been backed up as *.gz file with the compression 9 "pg_dump -Z 9". What is the right format of restore this file when needed? Can I run the restore from a compressed file or I need to unzip the file first, then run pg_restore? Thanks
On 7/13/20 12:12 PM, Julie Nishimura wrote: > Hello there, > One of our PostgreSQL 9.4.1 databases has been backed up as *.gz file > with the compression 9 "pg_dump -Z 9". What is the right format of > restore this file when needed? Can I run the restore from a compressed > file or I need to unzip the file first, then run pg_restore? Thanks It depends on whether you dumped using the custom format -Fc or plain(no -F or -Fp). If the custom format then you run pg_restore against it. If the plain format then you will to unzip first then feed the file to psql. -- Adrian Klaver adrian.klaver@aklaver.com
On 7/13/20 2:32 PM, Adrian Klaver wrote: > On 7/13/20 12:12 PM, Julie Nishimura wrote: >> Hello there, >> One of our PostgreSQL 9.4.1 databases has been backed up as *.gz file >> with the compression 9 "pg_dump -Z 9". What is the right format of >> restore this file when needed? Can I run the restore from a compressed >> file or I need to unzip the file first, then run pg_restore? Thanks > > It depends on whether you dumped using the custom format -Fc or plain(no > -F or -Fp). If the custom format then you run pg_restore against it. If > the plain format then you will to unzip first then feed the file to psql. What about this? gunzip -c | foo.sql.gz | psql -- Angular momentum makes the world go 'round.
On 7/13/20 2:56 PM, Ron wrote: > On 7/13/20 2:32 PM, Adrian Klaver wrote: >> On 7/13/20 12:12 PM, Julie Nishimura wrote: >>> Hello there, >>> One of our PostgreSQL 9.4.1 databases has been backed up as *.gz >>> file with the compression 9 "pg_dump -Z 9". What is the right format >>> of restore this file when needed? Can I run the restore from a >>> compressed file or I need to unzip the file first, then run >>> pg_restore? Thanks >> >> It depends on whether you dumped using the custom format -Fc or >> plain(no -F or -Fp). If the custom format then you run pg_restore >> against it. If the plain format then you will to unzip first then feed >> the file to psql. > > What about this? > gunzip -c | foo.sql.gz | psql > gunzip -c | test_plain.gz | psql -d test_gz -U postgres bash: test_plain.gz: command not found gzip: compressed data not read from a terminal. Use -f to force decompression. For help, type: gzip -h Null display is "NULL". I think what you want is: gunzip -c test_plain.gz | psql -d test_gz -U postgres Null display is "NULL". SET SET SET SET SET set_config ------------ (1 row) SET SET SET SET CREATE SCHEMA .... In any case that will only work if the *.gz file is a compressed plain text format. My suspicion is it is, still I had to allow the possibility that it is a custom format file that someone hung a gz extension on. -- Adrian Klaver adrian.klaver@aklaver.com
On 7/13/20 7:37 PM, Adrian Klaver wrote: > On 7/13/20 2:56 PM, Ron wrote: >> On 7/13/20 2:32 PM, Adrian Klaver wrote: >>> On 7/13/20 12:12 PM, Julie Nishimura wrote: >>>> Hello there, >>>> One of our PostgreSQL 9.4.1 databases has been backed up as *.gz file >>>> with the compression 9 "pg_dump -Z 9". What is the right format of >>>> restore this file when needed? Can I run the restore from a compressed >>>> file or I need to unzip the file first, then run pg_restore? Thanks >>> >>> It depends on whether you dumped using the custom format -Fc or plain(no >>> -F or -Fp). If the custom format then you run pg_restore against it. If >>> the plain format then you will to unzip first then feed the file to psql. >> >> What about this? >> gunzip -c | foo.sql.gz | psql >> > > gunzip -c | test_plain.gz | psql -d test_gz -U postgres > bash: test_plain.gz: command not found > gzip: compressed data not read from a terminal. Use -f to force > decompression. > For help, type: gzip -h > Null display is "NULL". > > I think what you want is: > > gunzip -c test_plain.gz | psql -d test_gz -U postgres > The hazards of not testing code before posting... :) -- Angular momentum makes the world go 'round.