Thread: pg_restore problem!!!

Mago-

pg_restore is used to restore a dump file created in one of the non-text
formats such as tar format.

To restore from a plain-text dump file, just pipe it into psql like so:

cat [filename] | psql [dbname]

-Nick

---------------------------------------------------------------------
Nick Fankhauser

    nickf@doxpop.com  Phone 1.765.965.7363  Fax 1.765.962.9788
doxpop - Court records at your fingertips - http://www.doxpop.com/
-----Original Message-----
From: pgsql-admin-owner@postgresql.org
[mailto:pgsql-admin-owner@postgresql.org]On Behalf Of Marvin
Sent: Tuesday, July 22, 2003 3:22 PM
To: pgsql-admin@postgresql.org
Subject: [ADMIN] pg_restore problem!!!


I made a back up file with this sentence:

pg_dump -U [superusername] -C -D -f [filename]  [dbname]

It seems to work fine. I opened the file and I saw all the commands to
insert every piece of data and reconstruct my schema. However when I try to
restore that file it gets me the following error:

C:\>pg_restore [filename]
pg_restore: [archiver] input file does not appear to be a valid archive

What I am doing wrong??
Any ideas??

Thank you in advanced.


MAGO


_________________________________________________________________
  IncrediMail - El E-mail ha evolucionado finalmente - Haga clic aquí

Hi,
Every time I have to specify a table name in a query I find myself prefixing
the name of the table with the schema name as in schema.table. Is there a
way to force the queries to look in one particular schema whenever I do not
specify a schema name?

thanks
dilan

Nick,

> To restore from a plain-text dump file, just pipe it into
> psql like so:
>
> cat [filename] | psql [dbname]

To nit-pick, this is a "useless use of cat".

In UNIX-land, simple input redirection will work much better:

  psql [dbname and various options] < [filename]

It's also less to type! :)

I also believe this works in certain Windows operating systems, which is what it appears Mago is using.

--------------------------
David Olbersen
iGuard Engineer
St. Bernard Software
11415 West Bernardo Court
San Diego, CA 92127
1-858-676-2277 x2152


> -----Original Message-----
> From: Nick Fankhauser [mailto:nickf@ontko.com]
> Sent: Tuesday, July 22, 2003 1:30 PM
> To: Marvin; pgsql-admin@postgresql.org
> Subject: Re: [ADMIN] pg_restore problem!!!
>
>
> Mago-
>
> pg_restore is used to restore a dump file created in one of
> the non-text
> formats such as tar format.
>
> To restore from a plain-text dump file, just pipe it into
> psql like so:
>
> cat [filename] | psql [dbname]
>
> -Nick
>
> ---------------------------------------------------------------------
> Nick Fankhauser
>
>     nickf@doxpop.com  Phone 1.765.965.7363  Fax 1.765.962.9788
> doxpop - Court records at your fingertips - http://www.doxpop.com/
> -----Original Message-----
> From: pgsql-admin-owner@postgresql.org
> [mailto:pgsql-admin-owner@postgresql.org]On Behalf Of Marvin
> Sent: Tuesday, July 22, 2003 3:22 PM
> To: pgsql-admin@postgresql.org
> Subject: [ADMIN] pg_restore problem!!!
>
>
> I made a back up file with this sentence:
>
> pg_dump -U [superusername] -C -D -f [filename]  [dbname]
>
> It seems to work fine. I opened the file and I saw all the commands to
> insert every piece of data and reconstruct my schema. However
> when I try to
> restore that file it gets me the following error:
>
> C:\>pg_restore [filename]
> pg_restore: [archiver] input file does not appear to be a
> valid archive
>
> What I am doing wrong??
> Any ideas??
>
> Thank you in advanced.
>
>
> MAGO
>
>
> _________________________________________________________________
>   IncrediMail - El E-mail ha evolucionado finalmente - Haga clic aquí
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 8: explain analyze is your friend
>

On Tue, 22 Jul 2003, Dilan Arumainathan wrote:

> Hi,
> Every time I have to specify a table name in a query I find myself prefixing
> the name of the table with the schema name as in schema.table. Is there a
> way to force the queries to look in one particular schema whenever I do not
> specify a schema name?

Just change search_path to include it:

psql
> set search_path=$user,public,mynewschema;

> To nit-pick, this is a "useless use of cat".
>
> In UNIX-land, simple input redirection will work much better:
>
>   psql [dbname and various options] < [filename]

Good point... to elaborate further, the reason I was in a piping mindset is
that with a large database, it also makes sense to compress on the fly to
avoid filesystem size limits, so I usually use this pair of commands for
backup/restore:

pg_dump dbname | gzip > dbdumpfile.gz

gunzip -c dbdumpfile | psql dbname

-Nick

On Tuesday 22 July 2003 22:52, Dilan Arumainathan wrote:
> Hi,
> Every time I have to specify a table name in a query I find myself
> prefixing the name of the table with the schema name as in schema.table. Is
> there a way to force the queries to look in one particular schema whenever
> I do not specify a schema name?

SET search_path TO ...

http://www.postgresql.org/docs/7.3/static/ddl-schemas.html

You can also use

ALTER USER user_name SET search_path to ...;

to make the setting permanent.


Ian Barwick
barwick@gmx.net

Nick,

> pg_dump dbname | gzip > dbdumpfile.gz
>
> gunzip -c dbdumpfile | psql dbname

If you're compressing why not use:

pg_dump -Fc dbname -f dbdumpfile
pg_restore dbdumpfile

?

Don't take this the wrong way, I'm genuinely curious! When I first started I didn't realize we did our dumps this way
andtried to gzip one of the dump files: and got 0% savings! 

If this is a way to reduce the size of my nightly dumps I'm all for it! :)

--------------------------
David Olbersen
iGuard Engineer
St. Bernard Software
11415 West Bernardo Court
San Diego, CA 92127
1-858-676-2277 x2152


> -----Original Message-----
> From: Nick Fankhauser [mailto:nickf@ontko.com]
> Sent: Tuesday, July 22, 2003 2:05 PM
> To: David Olbersen; pgsql-admin@postgresql.org
> Subject: RE: [ADMIN] pg_restore problem!!!
>
>
>
> > To nit-pick, this is a "useless use of cat".
> >
> > In UNIX-land, simple input redirection will work much better:
> >
> >   psql [dbname and various options] < [filename]
>
> Good point... to elaborate further, the reason I was in a
> piping mindset is
> that with a large database, it also makes sense to compress
> on the fly to
> avoid filesystem size limits, so I usually use this pair of
> commands for
> backup/restore:
>
> pg_dump dbname | gzip > dbdumpfile.gz
>
> gunzip -c dbdumpfile | psql dbname
>
> -Nick
>
>

Hi David-

I don't use the -Fc or -Ft format exclusively because I ran into dependency
problems with the order of object creation  when trying to do a full
database restore using pg_restore and these formats.

Since we have a fair amount of space, I do both a tar format and a
plain-text format dump every night. This gives me the option of quickly
restoring the entire database from the plain text version (which has no
dependency problems), but then also easily restoring a single object or
group of objects using pg_restore from the tar format. I haven't explored
using the c (custom/compressed) format because when I inquired about the
dependency problems I encountered with the tar format, I learned that this
was a known problem in every format except plain text.

If you're using only -Fc format, you might want to try running a full
restore to make sure you don't have to reorder elements during recovery. I
opted to do both formats because our database is subject to regular
structure changes, so I can't tell how elements will have to be re-ordered
in advance. I like all of my restore pre-plans to be simple enough to
execute quickly and with little thought while many people are talking to me.
(Because it is well-known among most users that distracting your SysAdmin
with frequent visits and phone calls  makes him work faster and ensures that
your system gets restored more quickly.)

-NF



> -----Original Message-----
> From: pgsql-admin-owner@postgresql.org
> [mailto:pgsql-admin-owner@postgresql.org]On Behalf Of David Olbersen
> Sent: Tuesday, July 22, 2003 4:15 PM
> To: pgsql-admin@postgresql.org
> Subject: Re: [ADMIN] pg_restore problem!!!
>
>
> Nick,
>
> > pg_dump dbname | gzip > dbdumpfile.gz
> >
> > gunzip -c dbdumpfile | psql dbname
>
> If you're compressing why not use:
>
> pg_dump -Fc dbname -f dbdumpfile
> pg_restore dbdumpfile
>
> ?
>
> Don't take this the wrong way, I'm genuinely curious! When I
> first started I didn't realize we did our dumps this way and
> tried to gzip one of the dump files: and got 0% savings!
>
> If this is a way to reduce the size of my nightly dumps I'm all for it! :)
>
> --------------------------
> David Olbersen
> iGuard Engineer
> St. Bernard Software
> 11415 West Bernardo Court
> San Diego, CA 92127
> 1-858-676-2277 x2152
>
>
> > -----Original Message-----
> > From: Nick Fankhauser [mailto:nickf@ontko.com]
> > Sent: Tuesday, July 22, 2003 2:05 PM
> > To: David Olbersen; pgsql-admin@postgresql.org
> > Subject: RE: [ADMIN] pg_restore problem!!!
> >
> >
> >
> > > To nit-pick, this is a "useless use of cat".
> > >
> > > In UNIX-land, simple input redirection will work much better:
> > >
> > >   psql [dbname and various options] < [filename]
> >
> > Good point... to elaborate further, the reason I was in a
> > piping mindset is
> > that with a large database, it also makes sense to compress
> > on the fly to
> > avoid filesystem size limits, so I usually use this pair of
> > commands for
> > backup/restore:
> >
> > pg_dump dbname | gzip > dbdumpfile.gz
> >
> > gunzip -c dbdumpfile | psql dbname
> >
> > -Nick
> >
> >
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>