pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
pg_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>
  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 13:45  Peter Eisentraut <peter_e@gmx.net>
  parent: Devi Munandar <devi@informatika.lipi.go.id>
  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>
  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:56  Jim C. Nasby <jnasby@pervasive.com>
  parent: 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-13 20:28  renneyt@yahoo.com <renneyt@yahoo.com>
  parent: 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-13 23:02  Jim C. Nasby <jnasby@pervasive.com>
  parent: 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-14 02:08  Tom Lane <tgl@sss.pgh.pa.us>
  parent: Jim C. Nasby <jnasby@pervasive.com>
  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-14 14:04  renneyt@yahoo.com <renneyt@yahoo.com>
  parent: Jim C. Nasby <jnasby@pervasive.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