Re: pg_restore problem!!!

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
>