Re: Is pg_restore in 10.6 working?

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