[KLUG Members] MySQL pgSQL backups

[Buist Justin]

> -----Original Message-----
> From: John Pesce [mailto:pescej@sprl.db.erau.edu]
> Sent: Monday, August 26, 2002 4:38 PM
> To: members@kalamazoolinux.org
> Subject: Re: [KLUG Members] MySQL pgSQL backups
> 
> 
> Hello again. As I mentioned before. I've inherited a mysql 
> and a postgres
> server and I need to combine them on a new server.
> I visted the postgresql.org admin manual. I'm completely new 
> to databases.
> The manual mentions that pg_dump backs up a database, but 
> that pg_dumpall
> backs everything up. Later on it mentions that pg_dumpall 
> really doesn't
> backup everything, it skips OIDs and "Large Objects".
> 
> 1) What are OIDs?

Object Identifiers.  Your DB -should- run fine if the OIDs change from installation to installation, but I've heard some people like to use OIDs to link rows and tables together rather than typical primary/foreign key columns.  It's not a very popular idea, so it should be fine if you don't' have OIDs.

> 2) What is a "Large Object"?

It's a type of column in a DB that is used to hold a lot of data... typically something over 8k I think.

> 3) How do I know if I have OIDs or "Large Objects"

You'd have to examine the whole database schema to determine that.  Or ask the initial developer.


> I ran, as user postgres, pg_dumpall >db_back
> 
> on the new RH 7.3 server I ran:
> 
> initdb -D /var/lib/pgsql/data
> 
> which seems to create a template0 and template1 database.
> 
> if I do a psql < db_back it gives me an error.
> psql: FATAL 1:  Database "postgres" does not exist in the 
> system catalog.
> 
> if I run psql -l on the old server it says there are three databases
> capacity, postgres and template1

Ack, if you've only got 3 db's don't bother with pg_dumpall .

pg_dump capacity > capacity.sql
pg_dump postgres > postgres.sql
pg_dump template1 > template1.sql 


copy files to new server.
psql template1 < template1.sql
etc... 
I would imagine for 'postgres' and 'capacity' you'll have to first createdb them.  If they already exist, dropdb && createdb them.

 
> if I do a psql -d template1 <db_back, like the manual gives 
> as an example,
> I get the error:
> 
> You are now connected to database template1.
> SELECT
> DELETE 0
> DROP
> DELETE 0
> You are now connected to database template1 as user postgres.
> CREATE DATABASE
> You are now connected to database postgres as user postgres.
> \connect: FATAL 1:  IDENT authentication failed for user "florian"
> 
> I thought the entire database including users was suppost to 
> be dumped??

Yes, but who's allowed to connect to the 'postmaster' service is stored in the pg_hba.conf file.  You'll have to tweak that, then once psql can talk to postmaster you can re-create your DBs.

Justin Buist