Thread: pg_restore a dump in -Fd format?

pg_restore a dump in -Fd format?

From

Ron

Date:

03 May 2018, 21:21:26

Hi,

v9.6.6

I created a backup using "pg_dump -Fd", but am having no luck trying to 
restore it, and the docs are AFAICT lacking in examples of how to restore 
-Fd dumps.

What's the secret sauce for this method?

Thanks

-- 
Angular momentum makes the world go 'round.

Re: pg_restore a dump in -Fd format?

From

"David G. Johnston"

Date:

03 May 2018, 21:59:06

On Thu, May 3, 2018 at 8:21 AM, Ron <ronljohnsonjr@gmail.com> wrote:

Hi,

v9.6.6

I created a backup using "pg_dump -Fd", but am having no luck trying to restore it, and the docs are AFAICT lacking in examples of how to restore -Fd dumps.

What's the secret sauce for this method?

What did you try?

David J.

Re: pg_restore a dump in -Fd format?

From

Ron

Date:

03 May 2018, 22:10:14

On 05/03/2018 10:59 AM, David G. Johnston wrote:

On Thu, May 3, 2018 at 8:21 AM, Ron <ronljohnsonjr@gmail.com> wrote:
Hi,

v9.6.6

I created a backup using "pg_dump -Fd", but am having no luck trying to restore it, and the docs are AFAICT lacking in examples of how to restore -Fd dumps.

What's the secret sauce for this method?

What did you try?

SRC=/path/to/dumpDB=mydatabaseecho `date` Begin restorepg_restore -vcCe --if-exists --jobs=2 --file=$SRC/DBecho `date` Eng restore

The nohup.out file looks like this:

Thu May 3 11:12:19 EDT 2018 Begin restore total 49752768drwxr-xr-x 2 postgres postgres 20480 May 3 10:20 ./drwxr-xr-x 4 postgres postgres 4096 May 3 10:33 ../-rw-r--r-- 1 postgres postgres 5 May 3 2018 10001.dat-rw-r--r-- 1 postgres postgres 5 May 3 2018 10003.dat-rw-r--r-- 1 postgres postgres 5 May 3 2018 9022.dat-rw-r--r-- 1 postgres postgres 5 May 3 2018 9024.dat-rw-r--r-- 1 postgres postgres 487586 May 3 2018 9026.dat-rw-r--r-- 1 postgres postgres 10193438 May 3 2018 9028.dat-rw-r--r-- 1 postgres postgres 5 May 3 2018 9030.dat[snip]-rw-r--r-- 1 postgres postgres 5 May 3 2018 9993.dat-rw-r--r-- 1 postgres postgres 5 May 3 2018 9995.dat-rw-r--r-- 1 postgres postgres 483 May 3 2018 9997.dat-rw-r--r-- 1 postgres postgres 5 May 3 2018 9999.dat-rw-r--r-- 1 postgres postgres 1846130 May 3 2018 toc.datpg_restore: [archiver] could not read input file: Bad file descriptor Thu May 3 11:12:19 EDT 2018 End restore

--
Angular momentum makes the world go 'round.

Re: pg_restore a dump in -Fd format?

From

"David G. Johnston"

Date:

03 May 2018, 22:28:24

On Thu, May 3, 2018 at 9:10 AM, Ron <ronljohnsonjr@gmail.com> wrote:

On 05/03/2018 10:59 AM, David G. Johnston wrote:
On Thu, May 3, 2018 at 8:21 AM, Ron <ronljohnsonjr@gmail.com> wrote:
Hi,

v9.6.6

I created a backup using "pg_dump -Fd", but am having no luck trying to restore it, and the docs are AFAICT lacking in examples of how to restore -Fd dumps.

What's the secret sauce for this method?

What did you try?
SRC=/path/to/dumpDB=mydatabaseecho `date` Begin restorepg_restore -vcCe --if-exists --jobs=2 --file=$SRC/DBecho `date` Eng restore

The nohup.out file looks like this:

Thu May 3 11:12:19 EDT 2018 Begin restore total 49752768drwxr-xr-x 2 postgres postgres 20480 May 3 10:20 ./drwxr-xr-x 4 postgres postgres 4096 May 3 10:33 ../-rw-r--r-- 1 postgres postgres 5 May 3 2018 10001.dat-rw-r--r-- 1 postgres postgres 5 May 3 2018 10003.dat-rw-r--r-- 1 postgres postgres 5 May 3 2018 9022.dat-rw-r--r-- 1 postgres postgres 5 May 3 2018 9024.dat-rw-r--r-- 1 postgres postgres 487586 May 3 2018 9026.dat-rw-r--r-- 1 postgres postgres 10193438 May 3 2018 9028.dat-rw-r--r-- 1 postgres postgres 5 May 3 2018 9030.dat[snip]-rw-r--r-- 1 postgres postgres 5 May 3 2018 9993.dat-rw-r--r-- 1 postgres postgres 5 May 3 2018 9995.dat-rw-r--r-- 1 postgres postgres 483 May 3 2018 9997.dat-rw-r--r-- 1 postgres postgres 5 May 3 2018 9999.dat-rw-r--r-- 1 postgres postgres 1846130 May 3 2018 toc.datpg_restore: [archiver] could not read input file: Bad file descriptor Thu May 3 11:12:19 EDT 2018 End restore

--file:

Specify output file for generated script, or for the listing when used with -l. Default is the standard output.

You are treating the --file option like it specifies the input file.

pg_restore [connection-option...] [option...] [filename]

The "filename" argument tacked on to the end of the command is where you specify the location of the dump.

David J.

Re: pg_restore a dump in -Fd format?

From

Ron

Date:

03 May 2018, 22:36:43

On 05/03/2018 11:28 AM, David G. Johnston wrote:

On Thu, May 3, 2018 at 9:10 AM, Ron <ronljohnsonjr@gmail.com> wrote:
On 05/03/2018 10:59 AM, David G. Johnston wrote:
On Thu, May 3, 2018 at 8:21 AM, Ron <ronljohnsonjr@gmail.com> wrote:
Hi,

v9.6.6

I created a backup using "pg_dump -Fd", but am having no luck trying to restore it, and the docs are AFAICT lacking in examples of how to restore -Fd dumps.

What's the secret sauce for this method?

What did you try?
SRC=/path/to/dumpDB=mydatabaseecho `date` Begin restorepg_restore -vcCe --if-exists --jobs=2 --file=$SRC/DBecho `date` Eng restore

The nohup.out file looks like this:

Thu May 3 11:12:19 EDT 2018 Begin restore total 49752768drwxr-xr-x 2 postgres postgres 20480 May 3 10:20 ./drwxr-xr-x 4 postgres postgres 4096 May 3 10:33 ../-rw-r--r-- 1 postgres postgres 5 May 3 2018 10001.dat-rw-r--r-- 1 postgres postgres 5 May 3 2018 10003.dat-rw-r--r-- 1 postgres postgres 5 May 3 2018 9022.dat-rw-r--r-- 1 postgres postgres 5 May 3 2018 9024.dat-rw-r--r-- 1 postgres postgres 487586 May 3 2018 9026.dat-rw-r--r-- 1 postgres postgres 10193438 May 3 2018 9028.dat-rw-r--r-- 1 postgres postgres 5 May 3 2018 9030.dat[snip]-rw-r--r-- 1 postgres postgres 5 May 3 2018 9993.dat-rw-r--r-- 1 postgres postgres 5 May 3 2018 9995.dat-rw-r--r-- 1 postgres postgres 483 May 3 2018 9997.dat-rw-r--r-- 1 postgres postgres 5 May 3 2018 9999.dat-rw-r--r-- 1 postgres postgres 1846130 May 3 2018 toc.datpg_restore: [archiver] could not read input file: Bad file descriptor Thu May 3 11:12:19 EDT 2018 End restore

--file:
Specify output file for generated script, or for the listing when used with -l. Default is the standard output.

You are treating the --file option like it specifies the input file.

pg_restore [connection-option...] [option...] [filename]

The "filename" argument tacked on to the end of the command is where you specify the location of the dump.

Since "pg_dump -Fd" needs a "--file" option to say where to put the dump directory, it seemed reasonable that "pg_restore -Fd" would need a --file to know where the dump directory is.

--
Angular momentum makes the world go 'round.

Re: pg_restore a dump in -Fd format?

From

Shreeyansh Dba

Date:

03 May 2018, 22:38:14

Hi Ron,

You can try the below command by specifying the database name separately.

pg_restore -vcCe --if-exists --jobs=2 -d $DB $SRC

Hope this helps..

On Thu, May 3, 2018 at 9:40 PM, Ron <ronljohnsonjr@gmail.com> wrote:

On 05/03/2018 10:59 AM, David G. Johnston wrote:
On Thu, May 3, 2018 at 8:21 AM, Ron <ronljohnsonjr@gmail.com> wrote:
Hi,

v9.6.6

I created a backup using "pg_dump -Fd", but am having no luck trying to restore it, and the docs are AFAICT lacking in examples of how to restore -Fd dumps.

What's the secret sauce for this method?

What did you try?
SRC=/path/to/dumpDB=mydatabaseecho `date` Begin restorepg_restore -vcCe --if-exists --jobs=2 --file=$SRC/DBecho `date` Eng restore

The nohup.out file looks like this:

Thu May 3 11:12:19 EDT 2018 Begin restore total 49752768drwxr-xr-x 2 postgres postgres 20480 May 3 10:20 ./drwxr-xr-x 4 postgres postgres 4096 May 3 10:33 ../-rw-r--r-- 1 postgres postgres 5 May 3 2018 10001.dat-rw-r--r-- 1 postgres postgres 5 May 3 2018 10003.dat-rw-r--r-- 1 postgres postgres 5 May 3 2018 9022.dat-rw-r--r-- 1 postgres postgres 5 May 3 2018 9024.dat-rw-r--r-- 1 postgres postgres 487586 May 3 2018 9026.dat-rw-r--r-- 1 postgres postgres 10193438 May 3 2018 9028.dat-rw-r--r-- 1 postgres postgres 5 May 3 2018 9030.dat[snip]-rw-r--r-- 1 postgres postgres 5 May 3 2018 9993.dat-rw-r--r-- 1 postgres postgres 5 May 3 2018 9995.dat-rw-r--r-- 1 postgres postgres 483 May 3 2018 9997.dat-rw-r--r-- 1 postgres postgres 5 May 3 2018 9999.dat-rw-r--r-- 1 postgres postgres 1846130 May 3 2018 toc.datpg_restore: [archiver] could not read input file: Bad file descriptor Thu May 3 11:12:19 EDT 2018 End restore

--
Angular momentum makes the world go 'round.

Re: pg_restore a dump in -Fd format?

From

Don Seiler

Date:

03 May 2018, 23:02:10

On Thu, May 3, 2018 at 11:36 AM, Ron <ronljohnsonjr@gmail.com> wrote:

Since "pg_dump -Fd" needs a "--file" option to say where to put the dump directory, it seemed reasonable that "pg_restore -Fd" would need a --file to know where the dump directory is.

It would make sense, but the docs do explicitly say it's for an output file. The output file is the SQL script generation option when not running on an actual database.

pg_restore can operate in two modes. If a database name is specified, pg_restore connects to that database and restores archive contents directly into the database. Otherwise, a script containing the SQL commands necessary to rebuild the database is created and written to a file or standard output. This script output is equivalent to the plain text output format of pg_dump. Some of the options controlling the output are therefore analogous to pg_dump options.

I'm curious if you've overwritten your dump files now with that output?

Don Seiler
www.seiler.us

Re: pg_restore a dump in -Fd format?

From

"David G. Johnston"

Date:

03 May 2018, 23:03:30

On Thu, May 3, 2018 at 9:36 AM, Ron <ronljohnsonjr@gmail.com> wrote:

Since "pg_dump -Fd" needs a "--file" option to say where to put the dump directory, it seemed reasonable that "pg_restore -Fd" would need a --file to know where the dump directory is.

It is a reasonable assumption - but when it failed the definition for --file in the pg_restore docs explains that is doesn't work that way.

Its too late to change it now even if its not particularly intuitive. And the complaint is not directory specific either - though for pg_dump you don't have the option of redirecting stdout like you do with a file output.

I could see a patch for pg_dump --file option (and maybe pg_restore) covering this non-symmetry being accepted, should one be put forth, but it doesn't come up particularly often and it errors out quickly.

On the code front I could see pg_restore being a bit more helpful if it sees "--file", and stdin is empty, that the resultant error hints to the user that maybe they mis-used the --file option.

David J.

Re: pg_restore a dump in -Fd format?

From

Ron

Date:

03 May 2018, 23:04:49

On 05/03/2018 12:02 PM, Don Seiler wrote:

On Thu, May 3, 2018 at 11:36 AM, Ron <ronljohnsonjr@gmail.com> wrote:

Since "pg_dump -Fd" needs a "--file" option to say where to put the dump directory, it seemed reasonable that "pg_restore -Fd" would need a --file to know where the dump directory is.

It would make sense, but the docs do explicitly say it's for an output file. The output file is the SQL script generation option when not running on an actual database.

pg_restore can operate in two modes. If a database name is specified, pg_restore connects to that database and restores archive contents directly into the database. Otherwise, a script containing the SQL commands necessary to rebuild the database is created and written to a file or standard output. This script output is equivalent to the plain text output format of pg_dump. Some of the options controlling the output are therefore analogous to pg_dump options.

I'm curious if you've overwritten your dump files now with that output?

After I got the correct command, it soon failed with a "file not found" error. I'm re-downloading everything now.

--
Angular momentum makes the world go 'round.