Re: Creating a clean database

On Tue, Jan 04, 2005 at 07:15:56AM -0500, Keith Worthington wrote:
>
> 1) pg_dump
> 2) psql create database testdb
> 3) pg_restore

It might be useful to see the exact pg_dump and pg_restore commands
you ran (all options and arguments).

> This seems to have the affect of keeping OIDs.

How did you determine this?  I'd expect that to be the case if
you used pg_dump's --oids option (or -o), but not otherwise.

> Are OIDs universal to an installation or specific to a database?
> I thought that when I created the new database the OIDs would start
> over at 0 but they didn't appear to do so.

See the "System Columns" section in the "Data Definition" chapter
of the documentation:

    OIDs are 32-bit quantities and are assigned from a single
    cluster-wide counter.  In a large or long-lived database, it
    is possible for the counter to wrap around.  Hence, it is bad
    practice to assume that OIDs are unique, unless you take steps
    to ensure that this is the case.

> I will check later but I am not sure whether or not the sequences
> were reset to 1 or not either.

If you did a restore, why would you want or expect the sequences
to be reset to 1?  That would contradict the purpose of a restore.

> My desire is to simply create a clean database.

What do you mean by a "clean" database?  How would a "clean" restored
database differ from the original database that you dumped, and why
would you want those differences?  What problem are you trying to
solve?

> I have read about OID counter wraparound and how that can be bad
> but I do not yet understand the implications of this issue.

OID wraparound means that you can't depend on OIDs being unique.
Code that makes that assumption could break.

Understand the difference between OID wraparound and transaction
ID wraparound -- see "Preventing transaction ID wraparound failures"
in the "Routine Database Maintenance Tasks" chapter for a discussion
of the latter.

> I suspect that I need to use pg_dump to output the SQL commands
> necessary to create the data dictionary and then run these commands via
> psql.  Do I just cat the file into psql?  i.e.
> cat data_dictionary.sql > psql -database NEWDB -username postgres

You can use "cat file | psql" but a few years ago that would have
won you a "Useless Use of Cat Award":

http://laku19.adsl.netsonic.fi/era/unix/award.html

Other ways are "psql < file" and "psql -f file".  See the psql
documentation and read your shell's documentation for an explanation
of input and output redirection.

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/