Backup questions

Started by Jason Tesserover 21 years ago6 messagesgeneral
Jump to latest
#1Jason Tesser
JTesser@nbbc.edu

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

#2Jason Tesser
JTesser@nbbc.edu
In reply to: Jason Tesser (#1)
Re: Backup questions

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

Show quoted text

message can get through to the mailing list cleanly

#3Alvaro Herrera
alvherre@dcc.uchile.cl
In reply to: Jason Tesser (#2)
Re: Backup questions

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)

#4Jason Tesser
JTesser@nbbc.edu
In reply to: Alvaro Herrera (#3)
Re: Backup questions

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.

Show quoted text

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)

#5Tom Lane
tgl@sss.pgh.pa.us
In reply to: Jason Tesser (#4)
Re: 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

#6Jason Tesser
JTesser@nbbc.edu
In reply to: Tom Lane (#5)
Re: Backup questions

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

Show quoted text

-----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