backup -restore question

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.