Thread: Backup questions
I have 2 questions. 1. What is the best way to back up a production box running Postgres nightly? 2. I thought the best way was to create a script that would use pg_dump to create a dump every night and then use restore if I need to. I did this but I am having a problem. Apparently there were a few Primary key Big Serial fields that had the name changed on them after they were created. The backup works fine but during the restore it fails because it thinks a relation is missing. Before restoring the database with the changed fields works fine, I just cannot get it to restore. Is there something in the background that Postgres is hanging on to after I renamed the fields that is preventing me from restoring? How can I work around this? Thank you Jason Tesser Software Development NMI 715-324-6900 x5273
Does anyone know what is going on with my 2nd question below? > -----Original Message----- > From: pgsql-general-owner@postgresql.org [mailto:pgsql-general- > owner@postgresql.org] On Behalf Of Jason Tesser > Sent: Thursday, July 29, 2004 11:09 AM > To: pgsql-general@postgresql.org > Subject: [GENERAL] Backup questions > > I have 2 questions. > > 1. What is the best way to back up a production box running Postgres > nightly? > > 2. I thought the best way was to create a script that would use pg_dump > to > create a dump every night and then use restore if I need to. I did this > but > I am having a problem. Apparently there were a few Primary key Big > Serial > fields that had the name changed on them after they were created. The > backup works fine but during the restore it fails because it thinks > a relation is missing. Before restoring the database with the changed > fields works fine, I just cannot get it to restore. Is there something > in the background that Postgres is hanging on to after I renamed the > fields that is preventing me from restoring? How can I work around > this? > > Thank you > Jason Tesser > Software Development > NMI 715-324-6900 x5273 > > > ---------------------------(end of broadcast)--------------------------- > TIP 3: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to majordomo@postgresql.org so that your > message can get through to the mailing list cleanly
On Fri, Jul 30, 2004 at 11:20:31AM -0500, Jason Tesser wrote: > Does anyone know what is going on with my 2nd question below? I think you'd have to show a specific example. > > 2. I thought the best way was to create a script that would use pg_dump to > > create a dump every night and then use restore if I need to. I did this > > but I am having a problem. Apparently there were a few Primary key Big > > Serial fields that had the name changed on them after they were created. > > The backup works fine but during the restore it fails because it thinks a > > relation is missing. Before restoring the database with the changed fields > > works fine, I just cannot get it to restore. Is there something in the > > background that Postgres is hanging on to after I renamed the fields that > > is preventing me from restoring? How can I work around this? -- Alvaro Herrera (<alvherre[@]dcc.uchile.cl>) "Linux transformó mi computadora, de una `máquina para hacer cosas', en un aparato realmente entretenido, sobre el cual cada día aprendo algo nuevo" (Jaime Salinas)
I am not sure how to be more specific. Basically when I try to restore a database that has been dumped in which the primary key to a table has had its name changed the restore fails because it thinks a relation is missing. > > On Fri, Jul 30, 2004 at 11:20:31AM -0500, Jason Tesser wrote: > > Does anyone know what is going on with my 2nd question below? > > I think you'd have to show a specific example. > > > > 2. I thought the best way was to create a script that would use > pg_dump to > > > create a dump every night and then use restore if I need to. I did > this > > > but I am having a problem. Apparently there were a few Primary key > Big > > > Serial fields that had the name changed on them after they were > created. > > > The backup works fine but during the restore it fails because it > thinks a > > > relation is missing. Before restoring the database with the changed > fields > > > works fine, I just cannot get it to restore. Is there something in > the > > > background that Postgres is hanging on to after I renamed the fields > that > > > is preventing me from restoring? How can I work around this? > > -- > Alvaro Herrera (<alvherre[@]dcc.uchile.cl>) > "Linux transformó mi computadora, de una `máquina para hacer cosas', > en un aparato realmente entretenido, sobre el cual cada día aprendo > algo nuevo" (Jaime Salinas)
"Jason Tesser" <JTesser@nbbc.edu> writes:
> I am not sure how to be more specific.
Could you show us the pg_dump -s (schema only) output?
regards, tom lane
As you can see below it fails on the sequence.
REVOKE ALL ON TABLE gbdetail_gbdetailid_seq FROM PUBLIC;
pg_restore: LOG: statement: GRANT INSERT,SELECT,UPDATE,DELETE ON TABLE
gbdetail_gbdetailid_seq TO jboss;
pg_restore: LOG: statement: --
-- TOC entry 275 (OID 883835)
-- Name: gbequipmentatt; Type: TABLE; Schema: public; Owner: dbdevel
-- Data Pos: 0
--
CREATE TABLE gbequipmentatt (
gbequipmentid bigserial NOT NULL,
equipmentlink1 character varying,
equipmentlink2 character varying
);
pg_restore: NOTICE: CREATE TABLE will create implicit sequence
"gbequipmentatt_gbequipmentid_seq" for "serial" column
"gbequipmentatt.gbequipmentid"
pg_restore: LOG: statement: --
-- TOC entry 276 (OID 883835)
-- Name: gbequipmentatt; Type: ACL; Schema: public; Owner: dbdevel
-- Data Pos: 0
--
REVOKE ALL ON TABLE gbequipmentatt FROM PUBLIC;
pg_restore: LOG: statement: GRANT INSERT,SELECT,UPDATE,DELETE ON TABLE
gbequipmentatt TO jboss;
pg_restore: LOG: statement: --
-- TOC entry 507 (OID 883835)
-- Name: gbequipmentatt_id_seq; Type: ACL; Schema: public; Owner:
dbdevel
-- Data Pos: 0
--
REVOKE ALL ON TABLE gbequipmentatt_id_seq FROM PUBLIC;
pg_restore: [archiver (db)] could not execute query: ERROR: relation
"gbequipmentatt_id_seq" does not exist
> -----Original Message-----
> From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
> Sent: Friday, July 30, 2004 1:35 PM
> To: Jason Tesser
> Cc: pgsql-general@postgresql.org
> Subject: Re: [GENERAL] Backup questions
>
> "Jason Tesser" <JTesser@nbbc.edu> writes:
> > I am not sure how to be more specific.
>
> Could you show us the pg_dump -s (schema only) output?
>
> regards, tom lane