Forum OpenACS Q&A: from pg7.0.2 to pg7.1.x questions?

Hi!

I have a couple of services running openacs with postgres 7.0.2, and
I would rebuild (erase everything and install again) my entire box,
so I will install postgres 7.1.1, here are the questions:

1. Some of the services are running openacs 3.2.2 & 3.2.5, do you see
any problem dumping the databases from pg7.0.2 to 7.1.1?

2. there is any special way of dump the DBs or just the usual pg_dump
dbname > dbname.out?

3. Can I use pg7.1.2 or isn't recommended yet for openacs?

Thanks,

Collapse
Posted by Erik Rogneby on
I recomend the -D flag.  It makes pretty robust dump files. (they will be large as well)

so if you are only going to dump individual databases then use:
<br>
pg_dump -D -d dbname > dbname.dmp
<p>
If you what to dump all of your databases then use:<br> "pg_dumpall -D > dumpall.dmp"

dumpall will create the users as well.

AND BY ALL MEANS use 7.1.2!  7.1.1 had a bug that sometimes will keep you from loading a datamodel.
<p>
Also if you dump individual database from 7.0.2 then you may need to do:
<ul>
<li>psql -d Name < /web/Name/www/doc/sql/postgres.sql
<li>createlang -d Name plpgsql
<li>createlang -d Name pltcl
</ul>

Collapse
Posted by Dan Wickstrom on
If you have time, you can use the -d -D flags, but beware, they might take a long time to reload, if the db is large (I'm talking over an hour or more).  The -d -D flags generate a dump file that uses insert statements.  Doing a plain dump results in a much smaller dumpfile that will load an order of magnitude faster.  A plain dump uses copy instead of inserts, so the whole copy is considered a transaction, and it is much faster.  Just do something like:

pg_dump -f dumpfile.dmp dbname

I'm not sure why the insert style dumpfile would be more robust, but it would probably be easier to edit if you need to change anything before you reload.