Обсуждение: pg_dumpall and pg_dumps
Hai,
Pg_dumpall is giving hard time in restoring the database.I opened the
dump and checked it . There are lot of problem in connectin and
reconnecting and plpgsql language call handler. Finally I have
decided to use pg_dump. It has also got the following bugs in it.
It is not setting the sequence values correctly. I took backup using
pg_dump and
restored it with psql. The sequence value in the database when I
took pg_dump is 11. BUt when I restored it, it is setting to
intial value i.e. one instead of current value of the sequence. I
opened the dump file and checked it. It is setting as follows.
SELECT setval ('"mysequence"', 1, 't'); Notonly that , there is
one more error. At the time of setting the sequence value, it has
to reconnect as the sequence owner. but it is not doing so. It
is keeping the previous owner connection and giving another error
that
psql:mytest.out:3069: ERROR: mysequence.setval: you don't have
permissions to set sequence mysequence. That means it is even
unable to set the wrong value i.e the SELECT setval
('"mysequence"', 1, 't');
I took the dump in postgresql7.1.3 and restored in
postgresql7.2.0. I have built the postgresql7.2.0 from CVS
repository which takes nightly snapshot.
Thanks
chava
s.chava@wcom.com (Srinivasa Rao Chava) writes:
> Pg_dumpall is giving hard time in restoring the database.I opened the
> dump and checked it . There are lot of problem in connectin and
> reconnecting and plpgsql language call handler.
This is not helpful; if you want us to fix things you're going to have
to give details.
There are known problems in reloading 7.1 dumps into 7.2 if you use
mixed-case user names or database names; might that be the source
of some of your problems? Are there other problems?
> SELECT setval ('"mysequence"', 1, 't'); Notonly that , there is
> one more error. At the time of setting the sequence value, it has
> to reconnect as the sequence owner.
Yup, this is a known bug in 7.1's pg_dump. It is fixed in current
sources, but we cannot retroactively make the problem go away :-(.
The only solution I can offer you at the moment is manual editing
of the dump script. When 7.2.1 comes out, a reasonable alternative
will be to use 7.2.1's pg_dump to perform the dump from 7.1.
regards, tom lane
On Thu, 21 Feb 2002 12:53:59 -0500
Tom Lane <tgl@sss.pgh.pa.us> wrote:
TL> There are known problems in reloading 7.1 dumps into 7.2 if you use
TL> mixed-case user names or database names; might that be the source
TL> of some of your problems? Are there other problems?
while you're at it, there are 2 other problems I've always experienced
with pg_dump/pg_dumpall:
1. it fails to restore databases owned by users who aren't allowed to
create databases: the dump contains a "CREATE USER x NOCREATEDB"
line at the top, then tries to \connect x and CREATE DATABASE y,
which obviously fails. It should always use CREATEDB for all users,
then remove the privilege at the end of the dump when appropriate.
2. you can't specify username and password on the command line: this
makes it impossible to backup automatically all the databases from a
cron script if you use password authentication, for instance.
It would be nice to have those fixed for good. ;-)
--
Simone Tellini
E-mail: tellini@areabusiness.it
http://www.areabusiness.it
Simone Tellini <tellini@areabusiness.it> writes:
> while you're at it, there are 2 other problems I've always experienced
> with pg_dump/pg_dumpall:
> 1. it fails to restore databases owned by users who aren't allowed to
> create databases: the dump contains a "CREATE USER x NOCREATEDB"
> line at the top, then tries to \connect x and CREATE DATABASE y,
> which obviously fails.
There is a plan to fix this, I believe. Someone's working on an OWNER
clause for CREATE DATABASE; once that exists, we'll make pg_dumpall
issue all the CREATE DATABASE commands as superuser.
> 2. you can't specify username and password on the command line: this
> makes it impossible to backup automatically all the databases from a
> cron script if you use password authentication, for instance.
I see no good way around that, except to not use password
authentication. (Even if it worked, it'd be a bad idea to embed the
password in the backup script.)
This is more practical than it used to be given the availability of
ident-style auth for Unix-socket connections (on many platforms) in 7.2.
Even if you don't have a platform with support for it, ident auth on
localhost TCP connections isn't an unreasonable way to go.
regards, tom lane
On Thu, 21 Feb 2002 13:44:03 -0500 Tom Lane <tgl@sss.pgh.pa.us> wrote: TL> > 2. you can't specify username and password on the command line: this TL> > makes it impossible to backup automatically all the databases from a TL> > cron script if you use password authentication, for instance. TL> TL> I see no good way around that, except to not use password actually, there's a way to patch pg_dumpall to allow for that: it's just not practical to have to modify it each time a new version is released. TL> authentication. (Even if it worked, it'd be a bad idea to embed the TL> password in the backup script.) Why? My backup script is readable/executable only from the postgres user. If you could read the password from the script, you would also be able to copy the whole postgres setup, modify it's config files, etc... I don't see any real problem in writing the postgres superuser password in the script, do you? TL> This is more practical than it used to be given the availability of TL> ident-style auth for Unix-socket connections (on many platforms) in 7.2. TL> Even if you don't have a platform with support for it, ident auth on TL> localhost TCP connections isn't an unreasonable way to go. if you're using postgres as a backend for a web server you might want to give your users access only to their database. You cannot use ident as the user will always be the same (ie. nobody, www, apache, whatever...) The only way I see is to use password authentication. -- Simone Tellini E-mail: tellini@areabusiness.it http://www.areabusiness.it