Re: [GENERAL] copy a database
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?= <[EMAIL PROTECTED]> 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 ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] copy a database
David Suela =?ISO-8859-1?Q?Fern=E1ndez?= <[EMAIL PROTECTED]> 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 ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] copy a database
El mié, 25-08-2004 a las 20:54, Tom Lane escribió: > David Suela =?ISO-8859-1?Q?Fern=E1ndez?= <[EMAIL PROTECTED]> 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 <[EMAIL PROTECTED]> xsto.info ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] copy a database
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 [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] copy a database
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 () "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) ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [GENERAL] copy a database
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 () 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. ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] copy a database
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 [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 8: explain analyze is your friend
