[KLUG Members] MySQL pgSQL backups

[Adam Williams]

>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?

Nothing you should ever have to worry about unless your app was written
by someone who needs to get smacked upside the head with a bat, no, make
that a sledge hammer.  Grep the source for "select oid".  If you don't
find anything (and you probably won't) just forget all about OIDs.  They
are used internally by the DB,  and they are managed automagically.

>2) What is a "Large Object"?

Also called a BLOB.  Primarily used to store untyped data such as
images, free form text, xml, PDFs, etc....  Check your schema to see if
you have any OID types.  "create table foobar (yaya oid, ...)".

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

See above, :)
 
> 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.

You have to create the databases first,  the restore doesn't do that
part.

>if I run psql -l on the old server it says there are three databases
>capacity, postgres and template1
>if I do a psql -d template1 <db_back, like the manual gives as an example,
>I get the error:

template1 is special.  It is created automatically and you shouldn't
mess with it unless you know what you are doing.  There is *NO* data in
there,  just funny things like stored procedures.  Do you use ODBC to
access the DB?  If so you might have added a bit to template1,  but that
is the only time it is typically used.

>I thought the entire database including users was suppost to be dumped??

Users are in the sys-tables, not the database.