Обсуждение: 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 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?

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

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