Skip to main content

SQL Dump Backup and Restore (PostgresSQL)

Updated

Note: This solution applies to Solaris or Linux with PostgreSQL.

Backup the Database

This method of backup uses the pg_dump utility, provided by PostgreSQL, to backup the Docushare database. pg_dump is a utility for saving a PostgreSQL database into a script or an archive file. The script files are in plain-text format and contain the SQL commands required to reconstruct the database to the state it was in at the time it was saved. There are alternative archive file formats to be used with pg_restore to rebuild the database.

This method will run while the database is active, however updates to the database while pg_dump is running will not be in the dump. For this reason, DocuShare should be in Read-Only mode before starting the backup.

1. Log in as root.

2. Put DocuShare into Read-Only mode.

# cd /Docushare/bin

# ./dsservice.sh state readonly

  1. Run the pg_dump utility to copy the database to a file you name.

#su - postgres

$ pg_dump -Ft Docushare | gzip > Docushare.tar.gz

$ exit

  1. Return DocuShare to a read-write mode.

# cd /Docushare/bin

# dsservice.sh state normal

Restore the Database

Use pg_restore to restore the backup file created from pg_dump.

  1. Log in as root.
  2. Stop DocuShare.

# cd /Docushare/bin

# ./stop_docushare.sh

  1. Use pg_restore to restore the database from the backup archive tar format file created above.

#su - postgres

$ createdb -E UNICODE <Docushare Database Name>
CREATE DATABASE

NOTE: If a default Docushare database already exist, you will need to first drop the database (dropdb Docushare).

$gzip -d Docushare.tar.gz
$ pg_restore -d Docushare Docushare.tar
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index 'dsobject_table_pkey' for table 'dsobject_table'
pg_restore: NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index 'dsobject_table_pkey' for table 'dsobject_table'
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index 'ce_table_pkey' for table 'ce_table'
pg_restore: NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index 'ce_table_pkey' for table 'ce_table'
$exit

  1. Start DocuShare.

# cd /Docushare/bin

# ./start_docushare.sh

File System Level Backup and Restore

Backup the Database

This method of backup directly copies the files that PostgreSQL uses to store database data. Backup all the tables and commit log files. Do not attempt to backup and restore only individual tables or databases.

To obtain consistent, usable backup files follow the steps in the correct order.

1. Stop DocuShare using the stop_docushare command.

# cd /Docushare/bin

# ./stop_docushare.sh

  1. Shut down the database. Use pg_ctl to stop the database

#su - postgres

$ pg_ctl -D /usr/local/pgsql/data stop

  1. Use your usual method of doing file system backups, such as the tar command, to copy the database files.

$ cd /usr/local/pgsql

$ tar -cvf backup.tar data

  1. Start the database with the pg_ctl command.

$ pg_ctl -D /usr/local/pgsql/data start

$ exit

  1. Start DocuShare.

# cd /Docushare/bin

# ./start_docushare.sh

Docushare now starting .

Docushare run .

Restore the Database

Install and configure DocuShare with PostgreSQL first and use your file system backup to overwrite the empty PostgreSQL database files.

1. Initialize PostgreSQL as described in the DocuShare installation process.

  1. Stop DocuShare using the stop_docushare command.

# cd /Docushare/bin

# ./stop_docushare.sh

  1. Shut down the database. Use pg_ctl to stop the database

#su - postgres

$ pg_ctl -D /usr/local/pgsql/data stop

  1. Change your current working directory to /usr/local/pgsql/data

$ cd /usr/local/pgsql

  1. Restore files from the backup file to the current working directory.

$ tar -xvf backup.tar

$ pg_ctl -D /usr/local/pgsql/data start

$ exit

  1. Start DocuShare.

# cd /Docushare/bin

# ./start_docushare.sh

Related articles