[Dspace-tech] Exporting/importing the PostgreSQL database?

[Stan Orlov]

Greetings!

I have DSpace running on a Windows XP machine and am migrating it to a
Windows 2003 server with the same structure. Installation went smoothly,
jspui is accessible, but I can't properly transfer the PostgreSQL database.
I tried to dump it using command line, backup using pgAdmin III, backup
using Navicat for PostgreSQL and also used export Wizard in PostgreSQL.
However, I can't import it properly using the same tools on the new
machine. I have different errors depending on the tool used and can post
them if that would help, but the root of the problem is probably in me
missing some step somewhere.

Since you probably have done this a million times, is there a bulletproof
PostgreSQL export/import command or utility you could recommend?

Cheers,
Stanislav Orlov

[Stan Orlov]

Forgot to mention - I am running the latest versions of DSpace (1.5.1) and
PostgreSQL (8.3) on both machines. And also the latest Apache and Tomcat 6.

Post by Stan Orlov
Greetings!
I have DSpace running on a Windows XP machine and am migrating it to a
Windows 2003 server with the same structure. Installation went smoothly,
jspui is accessible, but I can't properly transfer the PostgreSQL database.
I tried to dump it using command line, backup using pgAdmin III, backup
using Navicat for PostgreSQL and also used export Wizard in PostgreSQL.
However, I can't import it properly using the same tools on the new
machine. I have different errors depending on the tool used and can post
them if that would help, but the root of the problem is probably in me
missing some step somewhere.
Since you probably have done this a million times, is there a bulletproof
PostgreSQL export/import command or utility you could recommend?
Cheers,
Stanislav Orlov

[Allen Lam]

The db schema is changed while dspace version is changed. You errors may
be caused by version mismatch between dspace and db.

I suggest you re-create the old version db on the new machine, then run
the upgrade scripts provided by dspace to upgrade the db schema upto 1.5.1

Hope this helps.

Regards,
Allen Lam.
HKU Hub Administrator, http://hub.hku.hk

Post by Stan Orlov
Forgot to mention - I am running the latest versions of DSpace (1.5.1)
and PostgreSQL (8.3) on both machines. And also the latest Apache and
Tomcat 6.
Greetings!
I have DSpace running on a Windows XP machine and am migrating it
to a Windows 2003 server with the same structure. Installation
went smoothly, jspui is accessible, but I can't properly transfer
the PostgreSQL database. I tried to dump it using command line,
backup using pgAdmin III, backup using Navicat for PostgreSQL and
also used export Wizard in PostgreSQL. However, I can't import it
properly using the same tools on the new machine. I have
different errors depending on the tool used and can post them if
that would help, but the root of the problem is probably in me
missing some step somewhere.
Since you probably have done this a million times, is there a
bulletproof PostgreSQL export/import command or utility you could
recommend?
Cheers,
Stanislav Orlov

[Stan Orlov]

Hi Allen,

Thank you for the suggestion. I just would like to confirm whether it
applies to the situation where dspace version hasn't changed? I have dspace
1.5.1 on both machines. Postgres versions are slightly different (8.3.5-1
on the old and 8.3.6-2 on the new), but it probably isn't a significant
difference?

Dspace notwithstanding, I am trying to figure out how any postgres database
(in this case: dspace) could be flawlessly exported to another machine with
pretty much the same software version.

Cheers,
Stan

The db schema is changed while dspace version is changed. You errors may be
caused by version mismatch between dspace and db.
I suggest you re-create the old version db on the new machine, then run the
upgrade scripts provided by dspace to upgrade the db schema upto 1.5.1
Hope this helps.
Regards,
Allen Lam.
HKU Hub Administrator, http://hub.hku.hk

Post by Stan Orlov
Forgot to mention - I am running the latest versions of DSpace (1.5.1) and
PostgreSQL (8.3) on both machines. And also the latest Apache and Tomcat 6.
Greetings!
I have DSpace running on a Windows XP machine and am migrating it
to a Windows 2003 server with the same structure. Installation
went smoothly, jspui is accessible, but I can't properly transfer
the PostgreSQL database. I tried to dump it using command line,
backup using pgAdmin III, backup using Navicat for PostgreSQL and
also used export Wizard in PostgreSQL. However, I can't import it
properly using the same tools on the new machine. I have
different errors depending on the tool used and can post them if
that would help, but the root of the problem is probably in me
missing some step somewhere.
Since you probably have done this a million times, is there a
bulletproof PostgreSQL export/import command or utility you could
recommend?
Cheers,
Stanislav Orlov

This communication, including any attached documentation, is intended only
for the person or entity to which it is addressed, and may contain
confidential, personal, and/or privileged information. Any unauthorized
disclosure, copying, or taking action on the contents is strictly
prohibited. If you have received this message in error, please contact us
immediately so we may correct our records. Please then delete or destroy the
original transmission and any subsequent reply. Thank you.

[Stan Orlov]

Another crisis averted :) I tried to export the database via Navicat's MS
Access format and it imported vi Navicat on the new box just fine (although
I'll have to re-assign primary keys, apparently). Before, I was trying to
export in a text delimited format, but it would mess up field lengths and
primary/foreign keys. So, it seems to be working. Thanks for your advice!

Cheers,
Stan

Post by Stan Orlov
Hi Allen,
Thank you for the suggestion. I just would like to confirm whether it
applies to the situation where dspace version hasn't changed? I have dspace
1.5.1 on both machines. Postgres versions are slightly different (8.3.5-1
on the old and 8.3.6-2 on the new), but it probably isn't a significant
difference?
Dspace notwithstanding, I am trying to figure out how any postgres database
(in this case: dspace) could be flawlessly exported to another machine with
pretty much the same software version.
Cheers,
Stan

Post by Allen Lam
The db schema is changed while dspace version is changed. You errors may
be caused by version mismatch between dspace and db.
I suggest you re-create the old version db on the new machine, then run
the upgrade scripts provided by dspace to upgrade the db schema upto 1.5.1
Hope this helps.
Regards,
Allen Lam.
HKU Hub Administrator, http://hub.hku.hk

Post by Stan Orlov
Forgot to mention - I am running the latest versions of DSpace (1.5.1)
and PostgreSQL (8.3) on both machines. And also the latest Apache and
Tomcat 6.
Greetings!
I have DSpace running on a Windows XP machine and am migrating it
to a Windows 2003 server with the same structure. Installation
went smoothly, jspui is accessible, but I can't properly transfer
the PostgreSQL database. I tried to dump it using command line,
backup using pgAdmin III, backup using Navicat for PostgreSQL and
also used export Wizard in PostgreSQL. However, I can't import it
properly using the same tools on the new machine. I have
different errors depending on the tool used and can post them if
that would help, but the root of the problem is probably in me
missing some step somewhere.
Since you probably have done this a million times, is there a
bulletproof PostgreSQL export/import command or utility you could
recommend?
Cheers,
Stanislav Orlov

This communication, including any attached documentation, is intended only
for the person or entity to which it is addressed, and may contain
confidential, personal, and/or privileged information. Any unauthorized
disclosure, copying, or taking action on the contents is strictly
prohibited. If you have received this message in error, please contact us
immediately so we may correct our records. Please then delete or destroy the
original transmission and any subsequent reply. Thank you.

[Allen Lam]

The all-time trusted tool for backup and restore is postgres' own
pg_dump and pg_restore

To see the command options:

pg_dump --help
pg_restore --help

It can be tricky to use because you have to put into a good combination
of flags. Some flags you may wish to use with pg_dump is -b and -Fc

Anyway, you have already have the problem solved. You can try out these
commands and options again for routine backup uses.

Regards,
Allen Lam.
HKU Hub Administrator, http://hub.hku.hk

Post by Stan Orlov
Another crisis averted :) I tried to export the database via Navicat's
MS Access format and it imported vi Navicat on the new box just fine
(although I'll have to re-assign primary keys, apparently). Before, I
was trying to export in a text delimited format, but it would mess up
field lengths and primary/foreign keys. So, it seems to be working.
Thanks for your advice!
Cheers,
Stan

[Mark H. Wood]

Well, here's what I use to snapshot the database every night:

/usr/bin/pg_dump -Ft -b dspace | gzip -9 > /var/lib/idea/db.tar.gz

Change the names and paths to suit your envorinment, of course. The
result should be suitable for loading with 'pg_restore'. It will
*not* include your database users, groups, and GRANTs, however, so
you'll have to set those up manually before restoring. You can use
the Pg superuser account or the database account that DSpace uses, as
the database user ("-U someuser") to run pg_dump. This method dumps
only the single database "dspace" -- change the name in the command if
you called it something else.

Another way, if you want to copy the entire database cluster
(databases, roles, GRANTs, and all) would be:

pg_dumpall > db.sql

This will produce a plain SQL script that should load everything you
had into the new database when fed to 'psql' thus:

psql -f db.sql

Run both of these as the Pg superuser. ("-U postgres" or whatever)