Is pg_restore in 10.6 working?

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
14 messages Options
Reply | Threaded
Open this post in threaded view
|

Is pg_restore in 10.6 working?

David-6
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.

I can connect with psql, and pg_dump works, but pg_restore never does anything when I call it.  It never even prompts for a password.

Here is my pg_hba.conf:
# "local" is for Unix domain socket connections only
local   all             all                                     ident map=super
# IPv4 local connections:
host    all             all             0.0.0.0/0               md5
# IPv6 local connections:
host    all             all             ::1/128                 ident
# Allow replication connections from localhost, by a user with the
# replication privilege.
local   replication     all                                     peer
host    replication     all             127.0.0.1/32            ident
host    replication     all             ::1/128                 ident

And my pg_ident.conf
# MAPNAME       SYSTEM-USERNAME         PG-USERNAME
super           ec2-user                postgres

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

I'm running 10.6.

thank you 


Reply | Threaded
Open this post in threaded view
|

Re: Is pg_restore in 10.6 working?

Joshua D. Drake
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.   *****


Reply | Threaded
Open this post in threaded view
|

Re: Is pg_restore in 10.6 working?

Tom Lane-2
In reply to this post by David-6
David <[hidden email]> 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

lup
Reply | Threaded
Open this post in threaded view
|

Re: Is pg_restore in 10.6 working?

lup

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

> David <[hidden email]> 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?


Reply | Threaded
Open this post in threaded view
|

Re: Is pg_restore in 10.6 working?

David-6
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 <[hidden email]> wrote:

On 11/12/18 11:44 AM, Tom Lane wrote:
> David <[hidden email]> 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?


lup
Reply | Threaded
Open this post in threaded view
|

Re: Is pg_restore in 10.6 working?

lup


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 <[hidden email]> wrote:

On 11/12/18 11:44 AM, Tom Lane wrote:
> David <[hidden email]> 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.
Reply | Threaded
Open this post in threaded view
|

Re: Is pg_restore in 10.6 working?

Tom Lane-2
Rob Sargent <[hidden email]> 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

lup
Reply | Threaded
Open this post in threaded view
|

Re: Is pg_restore in 10.6 working?

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

> Rob Sargent <[hidden email]> 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.


Reply | Threaded
Open this post in threaded view
|

Re: Is pg_restore in 10.6 working?

Adrian Klaver-4
In reply to this post by David-6
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 <[hidden email]
> <mailto:[hidden email]>> wrote:
>
>
>     On 11/12/18 11:44 AM, Tom Lane wrote:
>      > David <[hidden email] <mailto:[hidden email]>> 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
[hidden email]

Reply | Threaded
Open this post in threaded view
|

Re: Is pg_restore in 10.6 working?

Adrian Klaver-4
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 <[hidden email]
> <mailto:[hidden email]>> 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
>     <[hidden email] <mailto:[hidden email]>
>      > <mailto:[hidden email] <mailto:[hidden email]>>> wrote:
>      >
>      >
>      >     On 11/12/18 11:44 AM, Tom Lane wrote:
>      >      > David <[hidden email] <mailto:[hidden email]>
>     <mailto:[hidden email] <mailto:[hidden email]>>> 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
>     [hidden email] <mailto:[hidden email]>
>


--
Adrian Klaver
[hidden email]

Reply | Threaded
Open this post in threaded view
|

Re: Is pg_restore in 10.6 working?

raf-6
In reply to this post by Tom Lane-2
Tom Lane wrote:

> David <[hidden email]> 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


Reply | Threaded
Open this post in threaded view
|

Re: Is pg_restore in 10.6 working?

raf-6
raf wrote:

> Tom Lane wrote:
>
> > David <[hidden email]> 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


Reply | Threaded
Open this post in threaded view
|

Re: Is pg_restore in 10.6 working?

David-6
In reply to this post by Adrian Klaver-4
Thanks, Adrian.  It's running now.

On Mon, Nov 12, 2018 at 5:05 PM Adrian Klaver <[hidden email]> wrote:
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 <[hidden email]
> <mailto:[hidden email]>> 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
>     <[hidden email] <mailto:[hidden email]>
>      > <mailto:[hidden email] <mailto:[hidden email]>>> wrote:
>      >
>      >
>      >     On 11/12/18 11:44 AM, Tom Lane wrote:
>      >      > David <[hidden email] <mailto:[hidden email]>
>     <mailto:[hidden email] <mailto:[hidden email]>>> 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
>     [hidden email] <mailto:[hidden email]>
>


--
Adrian Klaver
[hidden email]
Reply | Threaded
Open this post in threaded view
|

Re: Is pg_restore in 10.6 working?

Adrian Klaver-4
In reply to this post by raf-6
On 11/12/18 2:07 PM, raf wrote:

> Tom Lane wrote:
>
>> David <[hidden email]> 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
[hidden email]