[ADMIN] 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.
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
[ADMIN] pg_dump restore problem
Hi admin i have problem with user authentification I've made a dump from PostgreSQL 7.2.2 under linux 8.0 with the command: pg_dump -a -b -Fc -f bakstore -o -v store and running success, and then i try to restore with the command: pg_restore -a -d store -Fc -o -v bakstrore and running unsuccess because i've got message: pg_restore:[archiver (db)] could not reconnect to database : FATAL 1 : IDENT authentification failed for user "phpuser" my post pg_hba.conf * TYPE DATABASEIP_ADDRESS MASK AUTH_TYPEAUTH_ARGUMENT hostall 127.0.0.1 255.255.255.255 trust hoststore 167.205.240.166 255.255.255.0 trust hostall 127.0.0.1 255.255.255.255identphpuser my post pg_ident.conf ** MAPIDENTPGUSERNAME phpuserphpuserpostgres NB: i make 'store' database pass through phpPgAdmin2.4.2 with username:"phpuser" and password:"" Best Regards, --devi munandar ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [ADMIN] pg_dump/restore problem
On Mon, Jun 12, 2006 at 04:51:18PM -0400, [EMAIL PROTECTED] 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 [EMAIL PROTECTED]
Pervasive Software http://pervasive.comwork: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
Re: [ADMIN] pg_dump/restore problem
Jim C. Nasby wrote:
On Mon, Jun 12, 2006 at 04:51:18PM -0400, [EMAIL PROTECTED] 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.
Re: [ADMIN] pg_dump/restore problem
On Tue, Jun 13, 2006 at 04:28:47PM -0400, [EMAIL PROTECTED] 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 [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [ADMIN] pg_dump/restore problem
"Jim C. Nasby" <[EMAIL PROTECTED]> writes:
> On Tue, Jun 13, 2006 at 04:28:47PM -0400, [EMAIL PROTECTED] 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
---(end of broadcast)---
TIP 1: 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: [ADMIN] pg_dump/restore problem
Jim C. Nasby wrote: On Tue, Jun 13, 2006 at 04:28:47PM -0400, [EMAIL PROTECTED] 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.
Re: [ADMIN] pg_dump restore problem
Devi Munandar writes: > MAPIDENTPGUSERNAME > phpuserphpuserpostgres With this setup you can connect if your OS user is "phpuser" and the user you want to connect as is "postgres". So log in as "phpuser" on you OS and run pg_restore with the option -U postgres. -- Peter Eisentraut [EMAIL PROTECTED] ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
