pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedpg_dump restore problem
8+ messages / 5 participants
[nested] [flat]
* pg_dump restore problem
@ 2003-03-30 10:28 Devi Munandar <devi@informatika.lipi.go.id>
2003-03-30 13:45 ` Re: pg_dump restore problem Peter Eisentraut <peter_e@gmx.net>
0 siblings, 1 reply; 8+ messages in thread
From: Devi Munandar @ 2003-03-30 10:28 UTC (permalink / raw)
To: pgsql-admin
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 DATABASE IP_ADDRESS MASK
AUTH_TYPE AUTH_ARGUMENT
host all 127.0.0.1
255.255.255.255 trust
host store 167.205.240.166 255.255.255.0
trust
host all 127.0.0.1
255.255.255.255 ident phpuser
my post pg_ident.conf
******************
MAP IDENT PGUSERNAME
phpuser phpuser postgres
NB: i make 'store' database pass through phpPgAdmin2.4.2 with
username:"phpuser" and password:""
Best Regards,
--devi munandar
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: pg_dump restore problem
2003-03-30 10:28 pg_dump restore problem Devi Munandar <devi@informatika.lipi.go.id>
@ 2003-03-30 13:45 ` Peter Eisentraut <peter_e@gmx.net>
0 siblings, 0 replies; 8+ messages in thread
From: Peter Eisentraut @ 2003-03-30 13:45 UTC (permalink / raw)
To: Devi Munandar <devi@informatika.lipi.go.id>; +Cc: pgsql-admin
Devi Munandar writes:
> MAP IDENT PGUSERNAME
> phpuser phpuser postgres
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 peter_e@gmx.net
^ permalink raw reply [nested|flat] 8+ messages in thread
* pg_dump/restore problem
@ 2006-06-12 20:51 renneyt@yahoo.com <renneyt@yahoo.com>
2006-06-12 20:56 ` Re: pg_dump/restore problem Jim C. Nasby <jnasby@pervasive.com>
0 siblings, 1 reply; 8+ messages in thread
From: renneyt@yahoo.com @ 2006-06-12 20:51 UTC (permalink / raw)
To: pgsql-admin
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.
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: pg_dump/restore problem
2006-06-12 20:51 pg_dump/restore problem renneyt@yahoo.com <renneyt@yahoo.com>
@ 2006-06-12 20:56 ` Jim C. Nasby <jnasby@pervasive.com>
2006-06-13 20:28 ` Re: pg_dump/restore problem renneyt@yahoo.com <renneyt@yahoo.com>
0 siblings, 1 reply; 8+ messages in thread
From: Jim C. Nasby @ 2006-06-12 20:56 UTC (permalink / raw)
To: renneyt@yahoo.com <renneyt@yahoo.com>; +Cc: pgsql-admin
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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: pg_dump/restore problem
2006-06-12 20:51 pg_dump/restore problem renneyt@yahoo.com <renneyt@yahoo.com>
2006-06-12 20:56 ` Re: pg_dump/restore problem Jim C. Nasby <jnasby@pervasive.com>
@ 2006-06-13 20:28 ` renneyt@yahoo.com <renneyt@yahoo.com>
2006-06-13 23:02 ` Re: pg_dump/restore problem Jim C. Nasby <jnasby@pervasive.com>
0 siblings, 1 reply; 8+ messages in thread
From: renneyt@yahoo.com @ 2006-06-13 20:28 UTC (permalink / raw)
To: Jim C. Nasby <jnasby@pervasive.com>; +Cc: pgsql-admin
Jim C. Nasby wrote:
> 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.
>
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: pg_dump/restore problem
2006-06-12 20:51 pg_dump/restore problem renneyt@yahoo.com <renneyt@yahoo.com>
2006-06-12 20:56 ` Re: pg_dump/restore problem Jim C. Nasby <jnasby@pervasive.com>
2006-06-13 20:28 ` Re: pg_dump/restore problem renneyt@yahoo.com <renneyt@yahoo.com>
@ 2006-06-13 23:02 ` Jim C. Nasby <jnasby@pervasive.com>
2006-06-14 02:08 ` Re: pg_dump/restore problem Tom Lane <tgl@sss.pgh.pa.us>
2006-06-14 14:04 ` Re: pg_dump/restore problem renneyt@yahoo.com <renneyt@yahoo.com>
0 siblings, 2 replies; 8+ messages in thread
From: Jim C. Nasby @ 2006-06-13 23:02 UTC (permalink / raw)
To: renneyt@yahoo.com <renneyt@yahoo.com>; +Cc: pgsql-admin
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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: pg_dump/restore problem
2006-06-12 20:51 pg_dump/restore problem renneyt@yahoo.com <renneyt@yahoo.com>
2006-06-12 20:56 ` Re: pg_dump/restore problem Jim C. Nasby <jnasby@pervasive.com>
2006-06-13 20:28 ` Re: pg_dump/restore problem renneyt@yahoo.com <renneyt@yahoo.com>
2006-06-13 23:02 ` Re: pg_dump/restore problem Jim C. Nasby <jnasby@pervasive.com>
@ 2006-06-14 02:08 ` Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 0 replies; 8+ messages in thread
From: Tom Lane @ 2006-06-14 02:08 UTC (permalink / raw)
To: Jim C. Nasby <jnasby@pervasive.com>; +Cc: renneyt@yahoo.com <renneyt@yahoo.com>; pgsql-admin
"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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: pg_dump/restore problem
2006-06-12 20:51 pg_dump/restore problem renneyt@yahoo.com <renneyt@yahoo.com>
2006-06-12 20:56 ` Re: pg_dump/restore problem Jim C. Nasby <jnasby@pervasive.com>
2006-06-13 20:28 ` Re: pg_dump/restore problem renneyt@yahoo.com <renneyt@yahoo.com>
2006-06-13 23:02 ` Re: pg_dump/restore problem Jim C. Nasby <jnasby@pervasive.com>
@ 2006-06-14 14:04 ` renneyt@yahoo.com <renneyt@yahoo.com>
1 sibling, 0 replies; 8+ messages in thread
From: renneyt@yahoo.com @ 2006-06-14 14:04 UTC (permalink / raw)
To: Jim C. Nasby <jnasby@pervasive.com>; +Cc: pgsql-admin
Jim C. Nasby wrote:
> 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.
^ permalink raw reply [nested|flat] 8+ messages in thread
end of thread, other threads:[~2006-06-14 14:04 UTC | newest]
Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-03-30 10:28 pg_dump restore problem Devi Munandar <devi@informatika.lipi.go.id>
2003-03-30 13:45 ` Peter Eisentraut <peter_e@gmx.net>
2006-06-12 20:51 pg_dump/restore problem renneyt@yahoo.com <renneyt@yahoo.com>
2006-06-12 20:56 ` Re: pg_dump/restore problem Jim C. Nasby <jnasby@pervasive.com>
2006-06-13 20:28 ` Re: pg_dump/restore problem renneyt@yahoo.com <renneyt@yahoo.com>
2006-06-13 23:02 ` Re: pg_dump/restore problem Jim C. Nasby <jnasby@pervasive.com>
2006-06-14 02:08 ` Re: pg_dump/restore problem Tom Lane <tgl@sss.pgh.pa.us>
2006-06-14 14:04 ` Re: pg_dump/restore problem renneyt@yahoo.com <renneyt@yahoo.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox