Обсуждение: pg_dump/restore problem
I am trying to move some data from an 8.0.7 instance to a 7.4.8 instance. I did a pg_dump and then did a pg_restore. For the most part the data transferred through to the older instance but I had problems with the sequence updates. SELECT pg_catalog.setval(pg_catalog.pg_get_serial_sequence('nw_foo', 'foo_id'), 678, true); ERROR: function pg_catalog.pg_get_serial_sequence("unknown", "unknown") does not exist HINT: No function matches the given name and argument types. You may need to add explicit type casts. I am looking for a an alternative to manually issuing alter sequence commands. Any help appreciated.
On Mon, Jun 12, 2006 at 04:51:18PM -0400, renneyt@yahoo.com wrote: > I am trying to move some data from an 8.0.7 instance to a 7.4.8 > instance. I did a pg_dump and then did a pg_restore. > For the most part the data transferred through to the older instance but > I had problems with the sequence updates. > > SELECT pg_catalog.setval(pg_catalog.pg_get_serial_sequence('nw_foo', > 'foo_id'), 678, true); > ERROR: function pg_catalog.pg_get_serial_sequence("unknown", "unknown") > does not exist > HINT: No function matches the given name and argument types. You may > need to add explicit type casts. > > I am looking for a an alternative to manually issuing alter sequence > commands. From http://www.postgresql.org/docs/8.1/interactive/release-8-0.html: # Add pg_get_serial_sequence() to return a SERIAL column's sequence name (Christopher) This allows automated scripts to reliably find the SERIAL sequence name. If you use a 7.x copy of pg_dump it might work, though there could be catalog changes that make that a problem. -- Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com Pervasive Software http://pervasive.com work: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
Jim C. Nasby wrote:
Does pg_get_serial_sequence() exist as plpgsql code? Where may I find it? I would like to retrofit it into a 7.4.8 PG database.
Thanks
On Mon, Jun 12, 2006 at 04:51:18PM -0400, renneyt@yahoo.com wrote:I am trying to move some data from an 8.0.7 instance to a 7.4.8 instance. I did a pg_dump and then did a pg_restore. For the most part the data transferred through to the older instance but I had problems with the sequence updates. SELECT pg_catalog.setval(pg_catalog.pg_get_serial_sequence('nw_foo', 'foo_id'), 678, true); ERROR: function pg_catalog.pg_get_serial_sequence("unknown", "unknown") does not exist HINT: No function matches the given name and argument types. You may need to add explicit type casts. I am looking for a an alternative to manually issuing alter sequence commands.>From http://www.postgresql.org/docs/8.1/interactive/release-8-0.html: # Add pg_get_serial_sequence() to return a SERIAL column's sequence name (Christopher)
Does pg_get_serial_sequence() exist as plpgsql code? Where may I find it? I would like to retrofit it into a 7.4.8 PG database.
Thanks
This allows automated scripts to reliably find the SERIAL sequence name. If you use a 7.x copy of pg_dump it might work, though there could be catalog changes that make that a problem.
On Tue, Jun 13, 2006 at 04:28:47PM -0400, renneyt@yahoo.com wrote: > Does pg_get_serial_sequence() exist as plpgsql code? Where may I find > it? I would like to retrofit it into a 7.4.8 PG database. No, but it shouldn't be terribly hard to do it in plpgsql; you just need to build the right sequence name (assuming you haven't been messing with the sequence names). -- Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com Pervasive Software http://pervasive.com work: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
"Jim C. Nasby" <jnasby@pervasive.com> writes: > On Tue, Jun 13, 2006 at 04:28:47PM -0400, renneyt@yahoo.com wrote: >> Does pg_get_serial_sequence() exist as plpgsql code? Where may I find >> it? I would like to retrofit it into a 7.4.8 PG database. > No, but it shouldn't be terribly hard to do it in plpgsql; you just need > to build the right sequence name (assuming you haven't been messing with > the sequence names). You could actually do it 100% correctly in plpgsql; it's really just a query into pg_depend to find the sequence dependent on the column. But if your need is just to restore one specific dump, I'd be inclined to edit the dump file. Most of the time, pg_get_serial_sequence('foo', 'bar') just results in 'foo_bar_seq'. regards, tom lane
Jim C. Nasby wrote:
Because of time and my unfamiliarity with pg_depend, I did a quick and dirty hack that worked for me.
My schema is foo.
CREATE or REPLACE FUNCTION foo.pg_get_serial_sequence(varchar(500), varchar(500)) RETURNS varchar(500) AS '
DECLARE
totcomments integer;
begin
return $1||''_''||$2||''_seq'';
end;
' language 'plpgsql';
,
Because it is a hosted ISP version of PG and I do not have write access to pg_catalog I had to run the backup dump through sed to change the schema from pg_catalog to foo.
cat pgsql.dmp | sed 's/pg_catalog.pg_get_serial_sequence/foo\.pg_get_serial_sequence/g'
> pgsql.dmp2
Thanks to all for all the suggestions.
On Tue, Jun 13, 2006 at 04:28:47PM -0400, renneyt@yahoo.com wrote:Does pg_get_serial_sequence() exist as plpgsql code? Where may I find it? I would like to retrofit it into a 7.4.8 PG database.No, but it shouldn't be terribly hard to do it in plpgsql; you just need to build the right sequence name (assuming you haven't been messing with the sequence names).
Because of time and my unfamiliarity with pg_depend, I did a quick and dirty hack that worked for me.
My schema is foo.
CREATE or REPLACE FUNCTION foo.pg_get_serial_sequence(varchar(500), varchar(500)) RETURNS varchar(500) AS '
DECLARE
totcomments integer;
begin
return $1||''_''||$2||''_seq'';
end;
' language 'plpgsql';
,
Because it is a hosted ISP version of PG and I do not have write access to pg_catalog I had to run the backup dump through sed to change the schema from pg_catalog to foo.
cat pgsql.dmp | sed 's/pg_catalog.pg_get_serial_sequence/foo\.pg_get_serial_sequence/g'
> pgsql.dmp2
Thanks to all for all the suggestions.