Обсуждение: Dumpall without OID
Hi, What could be the possible damage if a database is migraged without the -o (OID) option? Integrity of data? What are the options to solve this problem if it is migrated this way? Shouldn't pg_dumpall work like an exact copy of db? What could have gone wrong if only these commands were used? pg_dumpall -v -U postgres | gzip > /var/lib/pgsql/dump.data.gz gunzip -c /var/lib/pgsql/dump.data.gz | psql -U postgres >> /tmp/import.log They were used to migrate from PG8.4 to PG9 Regards, Girts
On 01/27/2011 04:52 AM, Girts Laudaks wrote: > Hi, > > What could be the possible damage if a database is migraged without the > -o (OID) option? Integrity of data? User OIDs are deprecated,so the main problem is whether you are currently using them as foreign keys. If you do not specify the -o switch and have OIDs specifically defined for a table(not the default) then new ones will be generated when the data is restored. If your schema/app depend on the old numbers then it would be a problem. > What are the options to solve this problem if it is migrated this way? > Shouldn't pg_dumpall work like an exact copy of db? > What could have gone wrong if only these commands were used? > > pg_dumpall -v -U postgres | gzip > /var/lib/pgsql/dump.data.gz > gunzip -c /var/lib/pgsql/dump.data.gz | psql -U postgres >> /tmp/import.log > > They were used to migrate from PG8.4 to PG9 > > Regards, > Girts > -- Adrian Klaver adrian.klaver@gmail.com
Well, seems that everything is OK. There are no OIDs used from the application side but they still appear in the database tables, this was what made the confusion. Thanks, G. On 2011.01.27. 21:33, Adrian Klaver wrote: > On 01/27/2011 04:52 AM, Girts Laudaks wrote: >> Hi, >> >> What could be the possible damage if a database is migraged without the >> -o (OID) option? Integrity of data? > > User OIDs are deprecated,so the main problem is whether you are > currently using them as foreign keys. If you do not specify the -o > switch and have OIDs specifically defined for a table(not the default) > then new ones will be generated when the data is restored. If your > schema/app depend on the old numbers then it would be a problem. > >> What are the options to solve this problem if it is migrated this way? >> Shouldn't pg_dumpall work like an exact copy of db? >> What could have gone wrong if only these commands were used? >> >> pg_dumpall -v -U postgres | gzip > /var/lib/pgsql/dump.data.gz >> gunzip -c /var/lib/pgsql/dump.data.gz | psql -U postgres >> >> /tmp/import.log >> >> They were used to migrate from PG8.4 to PG9 >> >> Regards, >> Girts >> > >
On Friday 28 January 2011 4:54:18 am Girts Laudaks wrote: > Well, seems that everything is OK. There are no OIDs used from the > application side but they still appear in the database tables, this was > what made the confusion. > > Thanks, > G. > OIDS on user tables have not been on by default since 8.0. Possible reasons why they exist: If the tables have been around since 8.0- OIDS will carry on with them. The table CREATE statements have the WITH OIDS clause. In postgresql.conf the default_with_oids setting is set on. In addition to the point I made yesterday, some old database drivers rely on OIDS to determine uniqueness. If you are positive that they are not needed you can use the ALTER TABLE table_name SET WITHOUT OIDS to remove the OID column from a table (http://www.postgresql.org/docs/9.0/interactive/sql-altertable.html) -- Adrian Klaver adrian.klaver@gmail.com
On 2011-01-27, Girts Laudaks <laudaksg@gmail.com> wrote: > Hi, > > What could be the possible damage if a database is migraged without the > -o (OID) option? Integrity of data? some things that used OID might fail to work. Postgres doesn't need them, does your application? > What are the options to solve this problem if it is migrated this way? > Shouldn't pg_dumpall work like an exact copy of db? It does if you use the correct version. > What could have gone wrong if only these commands were used? > > pg_dumpall -v -U postgres | gzip > /var/lib/pgsql/dump.data.gz > gunzip -c /var/lib/pgsql/dump.data.gz | psql -U postgres >> /tmp/import.log > > They were used to migrate from PG8.4 to PG9 that should work if you use the version 9 pg_dumpall -- ⚂⚃ 100% natural