Thread: copy a database
Hi!
Is it possible to copy/migrate one database from one postgres server to
other copying just the files in /var/lib/postgres/data/ ?
I can't use pg_dump because it give me an error. I think than the
instalation of postgres is corrupt because it give me a lot of error
like "ERROR: relation "pg_catalog.pg_user" does not exist".
Thanks,
Suela.
In an attempt to throw the authorities off his trail, dsuela@xsto.info (David Suela Fernández) transmitted: > Is it possible to copy/migrate one database from one postgres server to > other copying just the files in /var/lib/postgres/data/ ? > > I can't use pg_dump because it give me an error. I think than the > instalation of postgres is corrupt because it give me a lot of error > like "ERROR: relation "pg_catalog.pg_user" does not exist". Copying files from a corrupted database will just get you a _second_ corrupt database. If it's corrupted, then the best you can do is to get what data you can out of the database, and put it into a NEW database. What you might try would be to dump it table by table: for table in this_table that_table other_table; do pg_dump -p $SOURCEPORT -t $table $SOURCEDB | psql -p $NEWPORT $NEWDB done If the database is corrupted, that's likely to partly break, but I doubt you'll do much better any other way. -- If this was helpful, <http://svcs.affero.net/rm.php?r=cbbrowne> rate me http://cbbrowne.com/info/lisp.html "Natives who beat drums to drive off evil spirits are objects of scorn to smart Americans who blow horns to break up traffic jams." -- Unknown
El mié, 25-08-2004 a las 14:58, Christopher Browne escribió: > In an attempt to throw the authorities off his trail, dsuela@xsto.info (David Suela Fernández) transmitted: > > Is it possible to copy/migrate one database from one postgres server to > > other copying just the files in /var/lib/postgres/data/ ? > > > > I can't use pg_dump because it give me an error. I think than the > > instalation of postgres is corrupt because it give me a lot of error > > like "ERROR: relation "pg_catalog.pg_user" does not exist". > > Copying files from a corrupted database will just get you a _second_ > corrupt database. > > If it's corrupted, then the best you can do is to get what data you > can out of the database, and put it into a NEW database. > > What you might try would be to dump it table by table: > > for table in this_table that_table other_table; do > pg_dump -p $SOURCEPORT -t $table $SOURCEDB | psql -p $NEWPORT $NEWDB > done The problem is that pg_dump always give me the next error: pg_dump: SQL command failed pg_dump: Error message from server: ERROR: relation "pg_user" does not exist pg_dump: The command was: SELECT (SELECT usename FROM pg_user WHERE usesysid = datdba) as dba, pg_encoding_to_char(encoding)as encoding, datpath FROM pg_database WHERE datname = 'ez3' I have the db scheme (tables and basic data), but what i need is the new data of the db. I can access the db and make queries. I think i'll make a script to pass data from the corrupt database to the new one. -- David Suela Fernández <dsuela@xsto.info> xsto.info <el acceso a la información>
On Wed, 2004-08-25 at 15:08, David Suela Fernández wrote: > The problem is that pg_dump always give me the next error: > > pg_dump: SQL command failed > pg_dump: Error message from server: ERROR: relation "pg_user" does not exist > pg_dump: The command was: SELECT (SELECT usename FROM pg_user WHERE usesysid = datdba) as dba, pg_encoding_to_char(encoding)as encoding, datpath FROM pg_database WHERE datname = 'ez3' Maybe recreating pg_user in the database will help. It is a global table, so if you have other databases where pg_user exists, copy the row from pg_class in that database to pg_class in the corrupted database. -- Oliver Elphick olly@lfix.co.uk Isle of Wight http://www.lfix.co.uk/oliver GPG: 1024D/A54310EA 92C8 39E7 280E 3631 3F0E 1EC0 5664 7A2F A543 10EA ======================================== "But the day of the Lord will come as a thief in the night. The heavens shall pass away with a great noise, and the elements shall melt with fervent heat, and the earth and the works that are therein shall be burned up." II Peter 3:10
On Wed, Aug 25, 2004 at 04:25:02PM +0100, Oliver Elphick wrote: > On Wed, 2004-08-25 at 15:08, David Suela Fernández wrote: > > > The problem is that pg_dump always give me the next error: > > > > pg_dump: SQL command failed > > pg_dump: Error message from server: ERROR: relation "pg_user" does not exist > > pg_dump: The command was: SELECT (SELECT usename FROM pg_user WHERE > > usesysid = datdba) as dba, pg_encoding_to_char(encoding) as > > encoding, datpath FROM pg_database WHERE datname = 'ez3' > > Maybe recreating pg_user in the database will help. It is a global > table, so if you have other databases where pg_user exists, copy the row > from pg_class in that database to pg_class in the corrupted database. No, pg_user is a view on pg_shadow ... SELECT pg_shadow.usename, pg_shadow.usesysid, pg_shadow.usecreatedb, pg_shadow.usesuper, pg_shadow.usecatupd, '********'::text AS passwd, pg_shadow.valuntil, pg_shadow.useconfig FROM pg_shadow; -- Alvaro Herrera (<alvherre[a]dcc.uchile.cl>) Y una voz del caos me habló y me dijo "Sonríe y sé feliz, podría ser peor". Y sonreí. Y fui feliz. Y fue peor.
On Wed, Aug 25, 2004 at 12:09:58PM -0400, Alvaro Herrera wrote: > No, pg_user is a view on pg_shadow ... > > SELECT pg_shadow.usename, pg_shadow.usesysid, pg_shadow.usecreatedb, > pg_shadow.usesuper, pg_shadow.usecatupd, '********'::text AS passwd, > pg_shadow.valuntil, pg_shadow.useconfig FROM pg_shadow; Sorry, this is obviously missing the CREATE VIEW pg_user AS part. -- Alvaro Herrera (<alvherre[a]dcc.uchile.cl>) "The important things in the world are problems with society that we don't understand at all. The machines will become more complicated but they won't be more complicated than the societies that run them." (Freeman Dyson)
On Wed, 2004-08-25 at 17:09, Alvaro Herrera wrote: > On Wed, Aug 25, 2004 at 04:25:02PM +0100, Oliver Elphick wrote: > > Maybe recreating pg_user in the database will help. It is a global > > table, so if you have other databases where pg_user exists, copy the row > > from pg_class in that database to pg_class in the corrupted database. > > No, pg_user is a view on pg_shadow ... Fingers! view ... table ... thingy What I meant is that, since it is global, recreating the item in pg_class as a copy of the entry in another database should possibly get rid of the pg_dump problem. If you create a new view, the result will presumably not be restorable. -- Oliver Elphick olly@lfix.co.uk Isle of Wight http://www.lfix.co.uk/oliver GPG: 1024D/A54310EA 92C8 39E7 280E 3631 3F0E 1EC0 5664 7A2F A543 10EA ======================================== "But the day of the Lord will come as a thief in the night. The heavens shall pass away with a great noise, and the elements shall melt with fervent heat, and the earth and the works that are therein shall be burned up." II Peter 3:10
David Suela =?ISO-8859-1?Q?Fern=E1ndez?= <dsuela@xsto.info> writes:
> The problem is that pg_dump always give me the next error:
> pg_dump: SQL command failed
> pg_dump: Error message from server: ERROR: relation "pg_user" does not exist
Have you tried re-creating that view?
CREATE VIEW pg_catalog.pg_user AS
SELECT
usename,
usesysid,
usecreatedb,
usesuper,
usecatupd,
'********'::text as passwd,
valuntil,
useconfig
FROM pg_shadow;
regards, tom lane
On Wed, Aug 25, 2004 at 05:57:35PM +0100, Oliver Elphick wrote: > On Wed, 2004-08-25 at 17:09, Alvaro Herrera wrote: > > On Wed, Aug 25, 2004 at 04:25:02PM +0100, Oliver Elphick wrote: > > > > Maybe recreating pg_user in the database will help. It is a global > > > table, so if you have other databases where pg_user exists, copy the row > > > from pg_class in that database to pg_class in the corrupted database. > > > > No, pg_user is a view on pg_shadow ... > > Fingers! view ... table ... thingy > > What I meant is that, since it is global, recreating the item in > pg_class as a copy of the entry in another database should possibly get > rid of the pg_dump problem. If you create a new view, the result will > presumably not be restorable. But pg_user is not global ... alvherre=# select relname, relisshared from pg_class where relname = 'pg_user'; relname | relisshared ---------+------------- pg_user | f (1 fila) -- Alvaro Herrera (<alvherre[a]dcc.uchile.cl>) "No deja de ser humillante para una persona de ingenio saber que no hay tonto que no le pueda enseñar algo." (Jean B. Say)
On Wed, 2004-08-25 at 20:08, Alvaro Herrera wrote: > On Wed, Aug 25, 2004 at 05:57:35PM +0100, Oliver Elphick wrote: > > What I meant is that, since it is global, ... > But pg_user is not global ... Yes; sorry, I was looking at the wrong thing. -- Oliver Elphick olly@lfix.co.uk Isle of Wight http://www.lfix.co.uk/oliver GPG: 1024D/A54310EA 92C8 39E7 280E 3631 3F0E 1EC0 5664 7A2F A543 10EA ======================================== "But the day of the Lord will come as a thief in the night. The heavens shall pass away with a great noise, and the elements shall melt with fervent heat, and the earth and the works that are therein shall be burned up." II Peter 3:10
Have you tried pg_dump --no-owner in order to avoid getting the ownership information? David Suela Fernández wrote: >El mié, 25-08-2004 a las 14:58, Christopher Browne escribió: > > >>In an attempt to throw the authorities off his trail, dsuela@xsto.info (David Suela Fernández) transmitted: >> >> >>>Is it possible to copy/migrate one database from one postgres server to >>>other copying just the files in /var/lib/postgres/data/ ? >>> >>>I can't use pg_dump because it give me an error. I think than the >>>instalation of postgres is corrupt because it give me a lot of error >>>like "ERROR: relation "pg_catalog.pg_user" does not exist". >>> >>> >>Copying files from a corrupted database will just get you a _second_ >>corrupt database. >> >>If it's corrupted, then the best you can do is to get what data you >>can out of the database, and put it into a NEW database. >> >>What you might try would be to dump it table by table: >> >>for table in this_table that_table other_table; do >> pg_dump -p $SOURCEPORT -t $table $SOURCEDB | psql -p $NEWPORT $NEWDB >>done >> >> > >The problem is that pg_dump always give me the next error: > >pg_dump: SQL command failed >pg_dump: Error message from server: ERROR: relation "pg_user" does not exist >pg_dump: The command was: SELECT (SELECT usename FROM pg_user WHERE usesysid = datdba) as dba, pg_encoding_to_char(encoding)as encoding, datpath FROM pg_database WHERE datname = 'ez3' > >I have the db scheme (tables and basic data), but what i need is the new >data of the db. I can access the db and make queries. > >I think i'll make a script to pass data from the corrupt database to the >new one. > > >
Attachment
El mié, 25-08-2004 a las 20:54, Tom Lane escribió: > David Suela =?ISO-8859-1?Q?Fern=E1ndez?= <dsuela@xsto.info> writes: > > The problem is that pg_dump always give me the next error: > > > pg_dump: SQL command failed > > pg_dump: Error message from server: ERROR: relation "pg_user" does not exist > > Have you tried re-creating that view? > > CREATE VIEW pg_catalog.pg_user AS > SELECT > usename, > usesysid, > usecreatedb, > usesuper, > usecatupd, > '********'::text as passwd, > valuntil, > useconfig > FROM pg_shadow; > > regards, tom lane It return: ERROR: permission denied to create "pg_catalog.pg_user" DETAIL: System catalog modifications are currently disallowed. How can i change this permissions? -- David Suela Fernández <dsuela@xsto.info> xsto.info <el acceso a la información>
David Suela =?ISO-8859-1?Q?Fern=E1ndez?= <dsuela@xsto.info> writes:
> It return:
> ERROR: permission denied to create "pg_catalog.pg_user"
> DETAIL: System catalog modifications are currently disallowed.
> How can i change this permissions?
IIRC, you need to run a standalone backend, with either the -O or -P
switch (I forget which). The REINDEX man page has some tips about this.
regards, tom lane
Have you tried to use "copy" to export the data from each table? As a last resort you could try this, since you said you can still select data from the tables. Make a list of tables then : sed -e "/^.*/copy & TO '&.sql';/" table.list | psql database This should create a file for each table ending with ".sql". Then re-create the table on the new DB and use "copy" to import the data from the files. David Suela Fernández wrote: >El mié, 25-08-2004 a las 20:54, Tom Lane escribió: > > >>David Suela =?ISO-8859-1?Q?Fern=E1ndez?= <dsuela@xsto.info> writes: >> >> >>>The problem is that pg_dump always give me the next error: >>> >>> >>>pg_dump: SQL command failed >>>pg_dump: Error message from server: ERROR: relation "pg_user" does not exist >>> >>> >>Have you tried re-creating that view? >> >>CREATE VIEW pg_catalog.pg_user AS >> SELECT >> usename, >> usesysid, >> usecreatedb, >> usesuper, >> usecatupd, >> '********'::text as passwd, >> valuntil, >> useconfig >> FROM pg_shadow; >> >> regards, tom lane >> >> > >It return: >ERROR: permission denied to create "pg_catalog.pg_user" >DETAIL: System catalog modifications are currently disallowed. > >How can i change this permissions? > > -- Guy Fraser
> > IIRC, you need to run a standalone backend, with either the -O or -P > switch (I forget which). The REINDEX man page has some tips about this. > I run a standalone backend and create the view. Now it works perfectly. Thanks a lot !!! -- David Suela Fernández <dsuela@xsto.info> xsto.info <el acceso a la información>