Is pg_restore in 10.6 working?

On 11/12/18 10:37 AM, David wrote:

I can connect with psql either of these two ways:
psql -U postgres
or
psql -h ip-172-31-62-127.ec2.internal -p 5432 -U postgres -W postgres
(Yes, it's an AWS server)

This pg_dump command works:
pg_dump -U postgres -f predata.sql -F p -v  -d prod_data

But a matching pg_restore command does nothing.
pg_restore -U postgres -f predata.sql -v

pg_restore -U postgres -v predata.sql

-f is used to output data from a backup file into predata.sql.

Usage:
  pg_restore [OPTION]... [FILE]

General options:
  -d, --dbname=NAME        connect to database name
  -f, --file=FILENAME      output file name
  -F, --format=c|d|t       backup file format (should be automatic)
  -l, --list               print summarized TOC of the archive
  -v, --verbose            verbose mode
  -V, --version            output version information, then exit
  -?, --help               show this help, then exit

I'm running 10.6.

thank you

--
Command Prompt, Inc. || http://the.postgres.company/ || @cmdpromptinc
*** A fault and talent of mine is to tell it exactly how it is. ***
PostgreSQL centered full stack support, consulting and development.
Advocate: @amplifypostgres || Learn: https://postgresconf.org
***** Unless otherwise stated, opinions are my own. *****

David <dlbarron28@gmail.com> writes:

I have some experience with different versions of Postgres, but I'm just
getting around to using pg_restore, and it's not working for me at all.
...
But a matching pg_restore command does nothing.
pg_restore -U postgres -f predata.sql -v

This command expects to read from stdin and write to predata.sql, so
it's not surprising that it's just sitting there. What you want
is something along the lines of

pg_restore -U postgres -d dbname -v <predata.sql

regards, tom lane

On 11/12/18 11:44 AM, Tom Lane wrote:

David <dlbarron28@gmail.com> writes:

I have some experience with different versions of Postgres, but I'm just
getting around to using pg_restore, and it's not working for me at all.
...
But a matching pg_restore command does nothing.
pg_restore -U postgres -f predata.sql -v

This command expects to read from stdin and write to predata.sql, so
it's not surprising that it's just sitting there. What you want
is something along the lines of

pg_restore -U postgres -d dbname -v <predata.sql

regards, tom lane

In this case, does the "General options" -f make sense? restoring to a file?

I'm not following your question. The pre-data and post-data sections each
go to an individual file, but the data section goes to a directory. I can
restore the files using psql, but it is the restore of the directory that
is hanging.

On Mon, Nov 12, 2018 at 2:28 PM Rob Sargent <robjsargent@gmail.com> wrote:

On 11/12/18 12:39 PM, David wrote:

I'm not following your question.  The pre-data and post-data sections
each go to an individual file, but the data section goes to a
directory.  I can restore the files using psql, but it is the restore
of the directory that is hanging.

On Mon, Nov 12, 2018 at 2:28 PM Rob Sargent <robjsargent@gmail.com
<mailto:robjsargent@gmail.com>> wrote:

On 11/12/18 11:44 AM, Tom Lane wrote:

David <dlbarron28@gmail.com <mailto:dlbarron28@gmail.com>> writes:

I have some experience with different versions of Postgres, but

I'm just

getting around to using pg_restore, and it's not working for me

at all.

...
But a matching pg_restore command does nothing.
pg_restore -U postgres -f predata.sql -v

This command expects to read from stdin and write to predata.sql, so
it's not surprising that it's just sitting there.  What you want
is something along the lines of

pg_restore -U postgres -d dbname -v <predata.sql

                       regards, tom lane

In this case, does the "General options" -f make sense? restoring
to a file?

If the top post it to my question about -f making sense, I was
responding to Tom's explanation.  He's correct of course. I'm just
wondering if pg-restore --help should include -f from the general
options.  I probable should have posed this to Joshua's reply.

Rob Sargent <robjsargent@gmail.com> writes:

On 11/12/18 12:39 PM, David wrote:

I'm not following your question.  The pre-data and post-data sections
each go to an individual file, but the data section goes to a
directory.  I can restore the files using psql, but it is the restore
of the directory that is hanging.

The other bit that I think David is missing is that pg_dump's default
output format is a plain-text SQL script, which is meant to be fed to
psql not pg_restore. To get something that pg_restore can work with,
you need to specify one of the non-text dump formats (typically, you'd
use -Fc or -Fd).

The situation in which you'd want to use "pg_restore -f" is if you
want to reconstruct a plain-text SQL script from one of the non-text
dump formats, rather than just restoring directly into a database.

regards, tom lane

On 11/12/18 2:06 PM, Tom Lane wrote:

Rob Sargent <robjsargent@gmail.com> writes:

On 11/12/18 12:39 PM, David wrote:

I'm not following your question.  The pre-data and post-data sections
each go to an individual file, but the data section goes to a
directory.  I can restore the files using psql, but it is the restore
of the directory that is hanging.

The other bit that I think David is missing is that pg_dump's default
output format is a plain-text SQL script, which is meant to be fed to
psql not pg_restore. To get something that pg_restore can work with,
you need to specify one of the non-text dump formats (typically, you'd
use -Fc or -Fd).

The situation in which you'd want to use "pg_restore -f" is if you
want to reconstruct a plain-text SQL script from one of the non-text
dump formats, rather than just restoring directly into a database.

regards, tom lane

Roger that. Thank you.

On 11/12/18 11:39 AM, David wrote:

I'm not following your question.  The pre-data and post-data sections
each go to an individual file, but the data section goes to a
directory.  I can restore the files using psql, but it is the restore of
the directory that is hanging.

That is not what you showed in your OP:

This pg_dump command works:
pg_dump -U postgres -f predata.sql -F p -v -d prod_data

But a matching pg_restore command does nothing.
pg_restore -U postgres -f predata.sql -v

We would need to see the commands for data section to be able to comment
further.

On Mon, Nov 12, 2018 at 2:28 PM Rob Sargent <robjsargent@gmail.com
<mailto:robjsargent@gmail.com>> wrote:

On 11/12/18 11:44 AM, Tom Lane wrote:

David <dlbarron28@gmail.com <mailto:dlbarron28@gmail.com>> writes:

I have some experience with different versions of Postgres, but

I'm just

getting around to using pg_restore, and it's not working for me

at all.

...
But a matching pg_restore command does nothing.
pg_restore -U postgres -f predata.sql -v

This command expects to read from stdin and write to predata.sql, so
it's not surprising that it's just sitting there.  What you want
is something along the lines of

pg_restore -U postgres -d dbname -v <predata.sql

                       regards, tom lane

In this case, does the "General options" -f make sense? restoring to
a file?

--
Adrian Klaverfile:///usr/share/applications/thunderbird.desktop
adrian.klaver@aklaver.com

On 11/12/18 1:58 PM, David wrote:

Please reply to list also.
Ccing list

Yes, that's what I get for writing emails while I'm doing 5 other things
at the same time.  So, let me try this again.
pg_dump is working when I use the following:
pg_dump -U postgres -F d -f /pgbackup/prod/data -v --section=data prod_data
To be clear, prod_data is the name of the database.
This works fine, I get /pgbackup/prod/data created and populated by
compressed files, as advertised.

How to I restore this?  Is there a specific combination of command line
options for this format?
I've tried -d prod_data and -f /pgbackup/prod/data -F d, but I get an error:
options -d and -f cannot be used together.

So I take -d off the command line and I get
[directory archiver] no output directory specified.
and if I use this I get nothing at all
pg_restore -U postgres -f /pgbackup/prod/data -v

So I'm confused.

Enlightenment:

https://www.postgresql.org/docs/10/app-pgrestore.html

-f filename
--file=filename

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

-d dbname
--dbname=dbname

Connect to database dbname and restore directly into the database.

filename

Specifies the location of the archive file (or directory, for a
directory-format archive) to be restored. If not specified, the standard
input is used.

So something like:

pg_restore -U postgres -v -d prod_data /pgbackup/prod/data

thanks again.

On Mon, Nov 12, 2018 at 4:39 PM Adrian Klaver <adrian.klaver@aklaver.com
<mailto:adrian.klaver@aklaver.com>> wrote:

On 11/12/18 11:39 AM, David wrote:

I'm not following your question.  The pre-data and post-data

sections

each go to an individual file, but the data section goes to a
directory.  I can restore the files using psql, but it is the

restore of

the directory that is hanging.

That is not what you showed in your OP:

This pg_dump command works:
pg_dump -U postgres -f predata.sql -F p -v  -d prod_data

But a matching pg_restore command does nothing.
pg_restore -U postgres -f predata.sql -v

We would need to see the commands for data section to be able to
comment
further.

On Mon, Nov 12, 2018 at 2:28 PM Rob Sargent

<robjsargent@gmail.com <mailto:robjsargent@gmail.com>

<mailto:robjsargent@gmail.com <mailto:robjsargent@gmail.com>>> wrote:

     On 11/12/18 11:44 AM, Tom Lane wrote:
      > David <dlbarron28@gmail.com <mailto:dlbarron28@gmail.com>

<mailto:dlbarron28@gmail.com <mailto:dlbarron28@gmail.com>>> writes:

      >> I have some experience with different versions of

Postgres, but

     I'm just
      >> getting around to using pg_restore, and it's not working

for me

     at all.
      >> ...
      >> But a matching pg_restore command does nothing.
      >> pg_restore -U postgres -f predata.sql -v
      > This command expects to read from stdin and write to

predata.sql, so

      > it's not surprising that it's just sitting there.  What

you want

      > is something along the lines of
      >
      > pg_restore -U postgres -d dbname -v <predata.sql
      >
      >                       regards, tom lane
      >

     In this case, does the "General options" -f make sense?

restoring to

     a file?

--
Adrian Klaverfile:///usr/share/applications/thunderbird.desktop
adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>

--
Adrian Klaver
adrian.klaver@aklaver.com

Tom Lane wrote:

David <dlbarron28@gmail.com> writes:

I have some experience with different versions of Postgres, but I'm just
getting around to using pg_restore, and it's not working for me at all.
...
But a matching pg_restore command does nothing.
pg_restore -U postgres -f predata.sql -v

This command expects to read from stdin and write to predata.sql, so
it's not surprising that it's just sitting there. What you want
is something along the lines of

pg_restore -U postgres -d dbname -v <predata.sql

regards, tom lane

Does that mean there's a bug in the usage message?

pg_restore --help says (admittedly on 9.5 but it's probably the same with 10.6):

Usage:
pg_restore [OPTION]... [FILE]

...

If no input file name is supplied, then standard input is used.

To me, that says that a filename on the command line after the options
will be read as the source of the restore. Only if it is absent would
stdin be used.

Apologies if the usage message for 10.6 doesn't say the same thing.

cheers,
raf

raf wrote:

Tom Lane wrote:

David <dlbarron28@gmail.com> writes:

I have some experience with different versions of Postgres, but I'm just
getting around to using pg_restore, and it's not working for me at all.
...
But a matching pg_restore command does nothing.
pg_restore -U postgres -f predata.sql -v

This command expects to read from stdin and write to predata.sql, so
it's not surprising that it's just sitting there. What you want
is something along the lines of

pg_restore -U postgres -d dbname -v <predata.sql

regards, tom lane

Does that mean there's a bug in the usage message?

pg_restore --help says (admittedly on 9.5 but it's probably the same with 10.6):

Usage:
pg_restore [OPTION]... [FILE]

...

If no input file name is supplied, then standard input is used.

To me, that says that a filename on the command line after the options
will be read as the source of the restore. Only if it is absent would
stdin be used.

Apologies if the usage message for 10.6 doesn't say the same thing.

Ah, I didn't notice the -f (output) option. Never mind.

cheers,
raf

Thanks, Adrian. It's running now.

On Mon, Nov 12, 2018 at 5:05 PM Adrian Klaver <adrian.klaver@aklaver.com>
wrote:

On 11/12/18 2:07 PM, raf wrote:

Tom Lane wrote:

David <dlbarron28@gmail.com> writes:

I have some experience with different versions of Postgres, but I'm just
getting around to using pg_restore, and it's not working for me at all.
...
But a matching pg_restore command does nothing.
pg_restore -U postgres -f predata.sql -v

This command expects to read from stdin and write to predata.sql, so
it's not surprising that it's just sitting there. What you want
is something along the lines of

pg_restore -U postgres -d dbname -v <predata.sql

regards, tom lane

Does that mean there's a bug in the usage message?

pg_restore --help says (admittedly on 9.5 but it's probably the same with 10.6):

Usage:
pg_restore [OPTION]... [FILE]

...

If no input file name is supplied, then standard input is used.

To me, that says that a filename on the command line after the options
will be read as the source of the restore. Only if it is absent would
stdin be used.

The example Tom showed was for using a plain text file as the source
instead of the default which is one of the custom formats.

Apologies if the usage message for 10.6 doesn't say the same thing.

cheers,
raf

--
Adrian Klaver
adrian.klaver@aklaver.com