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).
>

The dump command was:
pg_dump IPADB  -Ft -v -U postgres -f ${IPA_TMP_DIR}/TEST.tar
Then I created a new database called IPA_TEST with pgadmin3 and finally I
restored the database with:
pg_restore --dbname=IPA_TEST --schema-only --superuser=postgres --verbose -U
postgres TEST.tar

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

Well, I made what was probably a foolish assumption.  When the database was
restored the OIDs kept increasing rather than starting at some low value (0?).

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

Okay I read that section over.  So OIDs are universal to an installation and
not specific to each database.  That was something that I didn't understand.
For lack of a contrary rason I created most tables with OIDs as I thought I
read somewhere that they improved performance.  Is there another reason for
using them?  Do they increase performance and if not then what good are they?
 The documentation implies that if you really want a row identifier use a
serial type.  Isn't a row identifier the primary key?  Sighhhh.  So much to
learn, so little time.

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

I wasn't trying to restore to an old condition but rather create a new
database.  I will expand further below.

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

By clean database I mean freshly built with no data and all internal
constructs in a like new condition.  Specifically all counters set back to
inital value and I had thought (prior to my education on OIDs) the OID counter
back to its inital value.  My objective is simple.  We have been developing a
new system for several months.  The data dictionary is nearly in its final
form.  Most needed functions have been built and there is a bunch of really
mutilated data laying around after testing.  I simply wish to extract the data
dictionary and build the production database.

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

I do not use OIDs at this time.  As I implied above I do not yet see what use
they are.  If I had to build my database over from scratch (which I am about
to do.) I would probably not even use them so I am eager to learn how they
might benefit me.

>
> 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 didn't even know there was a xact identifier.  Still more to learn...

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

Ahh, the useless use of cat.  I ran into that a number of times on the sed
list but was never accused of it.  Not yet understanding what I can pipe into
and out of or redirect I have been lead like a lamb to the slaughter... :-)

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

Well, now that my education has progressed some more I believe that I will try
the following.
1) Use pgadmin to create NEWDB
2) Run the following:  pg_dump IPADB --format=p --schema-only --verbose -U
postgres > psql --dbname NEWDB --username postgres

Any further comments prior to my attempt tomorrow morning will be appreciated.
 Especially those that help me to avert disaster! ;-)

Kind Regards,
Keith

______________________________________________
99main Internet Services http://www.99main.com