Thread: 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