pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Constructing a backup routine
3+ messages / 3 participants
[nested] [flat]

* Constructing a backup routine
@ 2001-12-18 11:50 Johnny Jørgensen <pgsql@halfahead.dk>
  2001-12-18 14:39 ` Re: Constructing a backup routine GB Clark II <postgres@vsservices.com>
  0 siblings, 1 reply; 3+ messages in thread

From: Johnny Jørgensen @ 2001-12-18 11:50 UTC (permalink / raw)
  To: pgsql-sql

I've been over the docs, Bruce's book, and a good part of the web, where
there are plenty documentation on COPY, \copy and pgdump, but I have yet
to make a working backup (and the system goes into production state in 3
weeks..). The problem is, i get my data moved out & in fine, but
sequences are reset? This is, of course, something of a problem, since
it breaks new inserts into unique colums from sequences after a restore.
Also, I've been putting a lot of work into some plpgsql functions, that
I have to load manually - there's only a handful, so it isn't too big a
problem, but all in all, to get a near-workable backup, takes me an hour
or so by hand..

What way do you big folks handle backing up your systems?

regards,
-- 
Johnny Jørgensen <pgsql@halfahead.dk>




^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: Constructing a backup routine
  2001-12-18 11:50 Constructing a backup routine Johnny Jørgensen <pgsql@halfahead.dk>
@ 2001-12-18 14:39 ` GB Clark II <postgres@vsservices.com>
  0 siblings, 0 replies; 3+ messages in thread

From: GB Clark II @ 2001-12-18 14:39 UTC (permalink / raw)
  To: Johnny Jørgensen <pgsql@halfahead.dk>; pgsql-sql

On Tuesday 18 December 2001 05:50, Johnny Jørgensen wrote:
> I've been over the docs, Bruce's book, and a good part of the web, where
> there are plenty documentation on COPY, \copy and pgdump, but I have yet
> to make a working backup (and the system goes into production state in 3
> weeks..). The problem is, i get my data moved out & in fine, but
> sequences are reset? This is, of course, something of a problem, since
> it breaks new inserts into unique colums from sequences after a restore.
> Also, I've been putting a lot of work into some plpgsql functions, that
> I have to load manually - there's only a handful, so it isn't too big a
> problem, but all in all, to get a near-workable backup, takes me an hour
> or so by hand..
>
> What way do you big folks handle backing up your systems?
>
> regards,
Hi,

I ran into the same problem and the only way I could get pg_dump to work was
to dump the entire database instead of just the tables.

GB

-- 
GB Clark II             | Roaming FreeBSD Admin
gclarkii@VSServices.COM | General Geek 
           CTHULU for President - Why choose the lesser of two evils?



^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: Constructing a backup routine
@ 2001-12-20 08:44 Kovacs Zoltan <kovacsz@pc10.radnoti-szeged.sulinet.hu>
  0 siblings, 0 replies; 3+ messages in thread

From: Kovacs Zoltan @ 2001-12-20 08:44 UTC (permalink / raw)
  To: pgsql@halfahead.dk; +Cc: pgsql-sql

In our production state system we usually do this:

$ pg_dump -xacnDO database_name | grep "^SELECT setval "

This gives SQL statements which can be reloaded into an arbitrary
database. A bit slow, IMHO a clever select on the system tables should
give you the same result much faster.

HTH, Zoltan

-- 
                         Kov\'acs, Zolt\'an
                         kovacsz@pc10.radnoti-szeged.sulinet.hu
                         http://www.math.u-szeged.hu/~kovzol
                         ftp://pc10.radnoti-szeged.sulinet.hu/home/kovacsz




^ permalink  raw  reply  [nested|flat] 3+ messages in thread


end of thread, other threads:[~2001-12-20 08:44 UTC | newest]

Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2001-12-18 11:50 Constructing a backup routine Johnny Jørgensen <pgsql@halfahead.dk>
2001-12-18 14:39 ` GB Clark II <postgres@vsservices.com>
2001-12-20 08:44 Re: Constructing a backup routine Kovacs Zoltan <kovacsz@pc10.radnoti-szeged.sulinet.hu>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox