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(dot)klaver(at)aklaver(dot)com
> <mailto:adrian(dot)klaver(at)aklaver(dot)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(at)gmail(dot)com <mailto:robjsargent(at)gmail(dot)com>
> > <mailto:robjsargent(at)gmail(dot)com <mailto:robjsargent(at)gmail(dot)com>>> wrote:
> >
> >
> >     On 11/12/18 11:44 AM, Tom Lane wrote:
> >      > David <dlbarron28(at)gmail(dot)com <mailto:dlbarron28(at)gmail(dot)com>
> <mailto:dlbarron28(at)gmail(dot)com <mailto:dlbarron28(at)gmail(dot)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(dot)klaver(at)aklaver(dot)com <mailto:adrian(dot)klaver(at)aklaver(dot)com>
>

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com