Обсуждение: pg_dumpall between Linux and FreeBSD.

I have 1 FreeBSD machine and 1 Linux, Red Hat, machine.
I will need to periodically do a pg_dumpall from the Linux machine to the
FreeBSD machine.

This has been a problem because on Linux the superuser ID is postgres and
on FreeBSD it is pgsl.

When I try to restore to the FreeBSD machine it crashes because it can not
connect to the postgres user ID. I replaced all occurrences of postgres
with pgsql to get the load to work, but it was very time consuming (2.9 GB
file).

Any suggestions on how to easily move data between these two systems?
I have only a handfull databases so doing individual pg_dumps may work,
but my fear is that if someone in my team makes a new DB (ie for new
development) that they may not tell me and I may not find out about it
until the machine has crashed and they come asking for a restore. :-)

On Wed, 11 Sep 2002, Don Isgitt wrote:

> Can you not just createuser postgres on the FreeBSD machine and give it
> privileges?

That would probably work.
How does a new ID should be created?
Enter name of user to add: postgres
Shall the new user be allowed to create databases? (y/n) y
Shall the new user be allowed to create more new users? (y/n) y

Do those two options make the user a super-user?

On Wed, Sep 11, 2002 at 03:37:57PM -0400, Francisco J Reyes wrote:
> I have 1 FreeBSD machine and 1 Linux, Red Hat, machine.
> I will need to periodically do a pg_dumpall from the Linux machine to the
> FreeBSD machine.
>
> This has been a problem because on Linux the superuser ID is postgres and
> on FreeBSD it is pgsl.
>
> When I try to restore to the FreeBSD machine it crashes because it can not
> connect to the postgres user ID. I replaced all occurrences of postgres
> with pgsql to get the load to work, but it was very time consuming (2.9 GB
> file).

I'd suggest using sed to do the replace on the fly. They other possibility
is to do a grep -v '\connect'. This will remove all the connect requests
from the input stream. This means that the owner of each object will not be
what it used to be, but with GRANTs set up correctly you won't even notice.

--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> There are 10 kinds of people in the world, those that can do binary
> arithmetic and those that can't.

Francisco!

Thursday, September 12, 2002, 12:37:57 AM, You wrote:

FJR> I have 1 FreeBSD machine and 1 Linux, Red Hat, machine.
FJR> I will need to periodically do a pg_dumpall from the Linux machine to the
FJR> FreeBSD machine.

FJR> This has been a problem because on Linux the superuser ID is postgres and
FJR> on FreeBSD it is pgsl.

FJR> When I try to restore to the FreeBSD machine it crashes because it can not
FJR> connect to the postgres user ID. I replaced all occurrences of postgres
FJR> with pgsql to get the load to work, but it was very time consuming (2.9 GB
FJR> file).

FJR> Any suggestions on how to easily move data between these two systems?

The best and the right way is to eliminate difference between
PostgreSQL accounts on your machines. You should either switch to
postgres user on BSD or pgsql on Linux.. This is quite simple, i
think..

Sincerely Yours,
Timur
mailto:itvthor@sdf.lonestar.org

On Thu, 12 Sep 2002, Martijn van Oosterhout wrote:

> is to do a grep -v '\connect'. This will remove all the connect requests
> from the input stream.

That will definitely work.
It would still be nice if in the future pg_dumpall had better support for
changing the user, like pg_dump/restore have.

On Thu, 12 Sep 2002, Timur V. Irmatov wrote:

> The best and the right way is to eliminate difference between
> PostgreSQL accounts on your machines. You should either switch to
> postgres user on BSD or pgsql on Linux.. This is quite simple, i
> think..

For this time I will eliminate the connects, but for the future I plan to
create the same account(s).

The only thing I am not sure is how to give superuser capabilities to a
new account.

Francisco J Reyes a écrit:
> I have 1 FreeBSD machine and 1 Linux, Red Hat, machine.
> I will need to periodically do a pg_dumpall from the Linux machine to the
> FreeBSD machine.
>
> This has been a problem because on Linux the superuser ID is postgres and
> on FreeBSD it is pgsl.
>
> When I try to restore to the FreeBSD machine it crashes because it can not
> connect to the postgres user ID. I replaced all occurrences of postgres
> with pgsql to get the load to work, but it was very time consuming (2.9 GB
> file).
>
> Any suggestions on how to easily move data between these two systems?
> I have only a handfull databases so doing individual pg_dumps may work,
> but my fear is that if someone in my team makes a new DB (ie for new
> development) that they may not tell me and I may not find out about it
> until the machine has crashed and they come asking for a restore. :-)

vipw

change the username *ONLY* (don't touch the IDs)

vi /etc/group

    change its groupname, if your user has its own group


that should be ok.

keep in mind that scripts doing such as 'su - pgsql' need an update too. (sed is your friend)

On Thu, Sep 12, 2002 at 09:45:26AM -0400, Francisco J Reyes wrote:
> The only thing I am not sure is how to give superuser capabilities to a
> new account.

CREATE USER user WITH createuser

A

--
----
Andrew Sullivan                         204-4141 Yonge Street
Liberty RMS                           Toronto, Ontario Canada
<andrew@libertyrms.info>                              M2P 2A8
                                         +1 416 646 3304 x110