Обсуждение: pg_dumpall doesn't work
This is what I get when I do a pg_dumpall. I have *no* idea what to do here. I'm trying to migrate from 7.0.3 to 7.2.1, and the manual said to use pg_dumpall for this rather than using pg_dump on each database (which works). *Please* help. [postgres@everest /tmp]$ pg_dumpall -u > file Password: psql: Password authentication failed for user 'postgres' \connect template1 select datdba into table tmp_pg_shadow from pg_database where datname = 'template1'; delete from pg_shadow where usesysid <> tmp_pg_shadow.datdba; drop table tmp_pg_shadow; copy pg_shadow from stdin; Password: psql: Password authentication failed for user 'postgres' \. delete from pg_group; copy pg_group from stdin; Password: psql: Password authentication failed for user 'postgres' \. Password: [postgres@everest /tmp]$ cat file \connect template1 select datdba into table tmp_pg_shadow from pg_database where datname = 'template1'; delete from pg_shadow where usesysid <> tmp_pg_shadow.datdba; drop table tmp_pg_shadow; copy pg_shadow from stdin; \. delete from pg_group; copy pg_group from stdin; \. [postgres@everest /tmp]$ psql -V psql (PostgreSQL) 7.0.3 contains readline, history, multibyte support Portions Copyright (c) 1996-2000, PostgreSQL, Inc Portions Copyright (c) 1996 Regents of the University of California Read the file COPYRIGHT or use the command \copyright to see the usage and distribution terms. [postgres@everest /tmp]$ -- Justin Georgeson UnBound Technologies, Inc. http://www.unboundtech.com Main 713.329.9330 Fax 713.460.4051 Mobile 512.789.1962 5295 Hollister Road Houston, TX 77040 Real Applications using Real Wireless Intelligence(tm)
Justin Georgeson <jgeorgeson@unboundtech.com> writes:
> This is what I get when I do a pg_dumpall. I have *no* idea what to do
> here.
It's a lot easier to do pg_dumpall if you are not using password
authentication. Consider downgrading your pg_hba setting to "trust" for
local connections while you do the dump. (In 7.2 you could perhaps use
ident, or at least secure the socket with filesystem access restrictions.)
> I'm trying to migrate from 7.0.3 to 7.2.1,
*Please* make that 7.2.3 not 7.2.1.
regards, tom lane
Tom Lane wrote: > Justin Georgeson <jgeorgeson@unboundtech.com> writes: > >>This is what I get when I do a pg_dumpall. I have *no* idea what to do >>here. > > > It's a lot easier to do pg_dumpall if you are not using password > authentication. Consider downgrading your pg_hba setting to "trust" for > local connections while you do the dump. (In 7.2 you could perhaps use > ident, or at least secure the socket with filesystem access restrictions.) I'll give that a try. > >>I'm trying to migrate from 7.0.3 to 7.2.1, > > > *Please* make that 7.2.3 not 7.2.1. Will look into to that too, especially since postgresql.org has RH RPMs. (I'm a total RH weenie). Sorry if I sounded whiney with the *please*, but the last few times I've emailed community support lists, I haven't received any response at all, even just to tell me to stop asking. Thanks for your quick response. -- Justin Georgeson UnBound Technologies, Inc. http://www.unboundtech.com Main 713.329.9330 Fax 713.460.4051 Mobile 512.789.1962 5295 Hollister Road Houston, TX 77040 Real Applications using Real Wireless Intelligence(tm)
I want to know where to find information about database maintenance as a developer. I'm distributing the executable along with the Definition of the database. After some time, I made changes to the executable and to some tables too. I have to apply the database changes to the production database and do the following: 1) Backup the database data, pg_dump is of no use because if the table design changed then the COPY table FROM stdin produced by pg_dump needs to be modified to accomodate the space for the new columns. 2) Disable the data integrity checking 3) Drop the table (this drops the Foreign Key constraints) 4) Create the table with the new data layout or definition 5) Insert in the new table the old values (including the new columns or dropping some columns) 6) Define the data integrity checking 7) Enable the data integrity checking (I'm not sure if this checks the initial integrity or just start checking with the future inserts and updates???) All this is done manually and have to do very detailed analisis and planning for each change with the customer. The question is, has somebody faced this kind of problems and found some automation possibly (even some hints to do scripts about this will be welcome) Thanks Jesus Sandoval
On Sat, 14 Dec 2002, Jesus Sandoval wrote: > following: > 1) Backup the database data, pg_dump is of no use because if the table design > changed then the COPY table FROM stdin produced by pg_dump needs to be > modified to accomodate the space for the new columns. Actually, you can use pg_dump with some special options. I handle schema changes like this: 1) Back up the database with a normal pg_dump, just in case. 2) Back it up again with pg_dump "-a -D" -- This produces a bunch of INSERT INTO... statements 3) Drop the database 4) Recreate the database with the new schema 5) Restore from the dump in step (2). As long as all columns in the old schema are still present in the new schema, it works. If you need to get rid of a column, then in step (4), create a database with the unneeded column, and then do a step (6) whereby you drop unneeded columns. The only problem is that restoring from (2) is slow; so I use a script to add a "BEGIN...COMMIT" wrapper around the dump to speed it up. -- David.
"David F. Skoll" escribió: > On Sat, 14 Dec 2002, Jesus Sandoval wrote: > > > following: > > 1) Backup the database data, pg_dump is of no use because if the table design > > changed then the COPY table FROM stdin produced by pg_dump needs to be > > modified to accomodate the space for the new columns. > > Actually, you can use pg_dump with some special options. I handle schema > changes like this: > > 1) Back up the database with a normal pg_dump, just in case. > 2) Back it up again with pg_dump "-a -D" -- This produces a bunch of > INSERT INTO... statements > 3) Drop the database > 4) Recreate the database with the new schema > 5) Restore from the dump in step (2). As long as all columns in the old > schema are still present in the new schema, it works. If you need to > get rid of a column, then in step (4), create a database with the unneeded > column, and then do a step (6) whereby you drop unneeded columns. > > The only problem is that restoring from (2) is slow; so I use a script to > add a "BEGIN...COMMIT" wrapper around the dump to speed it up. > > -- > David. > > ---------------------------(end of broadcast)--------------------------- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org) Ok, thanks I had no clue about where to begin with this. I'll try it. btw, thanks for your contributions to the open source comunity, I'm actually using the ppp with my ADSL Modem, and found your ssh presentation very interesting, I'm using it to reach an internal net from a ISP provider (practical magic with ssh). Jesus Sandoval
Justin Georgeson wrote: > > > Tom Lane wrote: > >> Justin Georgeson <jgeorgeson@unboundtech.com> writes: >> >>> This is what I get when I do a pg_dumpall. I have *no* idea what to >>> do here. >> >> >> >> It's a lot easier to do pg_dumpall if you are not using password >> authentication. Consider downgrading your pg_hba setting to "trust" for >> local connections while you do the dump. (In 7.2 you could perhaps use >> ident, or at least secure the socket with filesystem access >> restrictions.) > > > I'll give that a try. That worked for the dump, but I still had to enter a password every time the script did a "\connect dbX userY". That was pretty annoying. My pg_hba.conf has two lines local all trust host all 127.0.0.1 255.255.255.255 reject The localhost host reject works. I just added the test if changes were being picked up dynamically, since I was still being prompted for a password. Also, I kept seeing this come up in the output while running the dumpall generated script. psql:dumpall.sql:40700: ERROR: No such attribute or function 'oid' CREATE psql:dumpall.sql:40702: ERROR: No such attribute or function 'oid' psql:dumpall.sql:40703: ERROR: No such attribute or function 'indislossy' CREATE psql:dumpall.sql:40705: ERROR: select rule's target entry 5 has different type from attribute sequence_last_value psql:dumpall.sql:40706: ERROR: No such attribute or function 'oid' psql:dumpall.sql:40707: ERROR: No such attribute or function 'oid' >> >>> I'm trying to migrate from 7.0.3 to 7.2.1, >> >> >> >> *Please* make that 7.2.3 not 7.2.1. > > > Will look into to that too, especially since postgresql.org has RH RPMs. > (I'm a total RH weenie). > rebuilt and installed the SRPMS with little fanfare, no local changes made to the spec files. -- Justin Georgeson UnBound Technologies, Inc. http://www.unboundtech.com Main 713.329.9330 Fax 713.460.4051 Mobile 512.789.1962 5295 Hollister Road Houston, TX 77040 Real Applications using Real Wireless Intelligence(tm)