Обсуждение: newbie psql - Backup/Restore - database

Hello all,

I just convert a MSQL database to PSQL and wondering if someone can help me to duplicate or restore a database in a short period.  Here is what I have done.

Backup <users> database:
   pg_dump -C -D -n -f backupDB.psql users

Duplicate <users> database:
   1. createdb test
   2. edit the backupDB.psql file and remove the first block regarding users DATABASE
   3. psql -a -e -f backupDB.psql test

Doing this way will take about a hour to duplicate a database with about 50,000 records.  Is there any other ways that you can help me to speed up the process?

All of your help will be greatly appreciated.

Dan  

On Mon, 21 Oct 2002, Nguyen, Dan wrote:

> Hello all,
>
> I just convert a MSQL database to PSQL and wondering if someone can help me
> to duplicate or restore a database in a short period.  Here is what I have
> done.
>
> Backup <users> database:
>    pg_dump -C -D -n -f backupDB.psql users
>
> Duplicate <users> database:
>    1. createdb test
>    2. edit the backupDB.psql file and remove the first block regarding users
> DATABASE
>    3. psql -a -e -f backupDB.psql test
>
>
> Doing this way will take about a hour to duplicate a database with about
> 50,000 records.  Is there any other ways that you can help me to speed up
> the process?

Not using -D to pg_dump will probably help since that'll use copy rather
than insert.

In my particular enviroment, fast backups were more important than 24
hour operation. I do the following.

1. rsync the raw database files to another directory.
   Note that this is DIRTY, the database is still operational. However,
   it gets about 95% of the data, WHILE the database is online.

2. Down the database

3. rsync again. Because  we got most of the data the first time, we're
   averaging about 10-15 seconds here.

4. start the database back up.

Total downtime per night over the last two weeks seems to be about 30
seconds. Which, for us, is nearly perfect.


On Mon, 2002-10-21 at 14:39, Nguyen, Dan wrote:
> Hello all,
>
> I just convert a MSQL database to PSQL and wondering if someone can help me
> to duplicate or restore a database in a short period.  Here is what I have
> done.
>
> Backup <users> database:
>    pg_dump -C -D -n -f backupDB.psql users
>
> Duplicate <users> database:
>    1. createdb test
>    2. edit the backupDB.psql file and remove the first block regarding users
> DATABASE
>    3. psql -a -e -f backupDB.psql test
>
>
> Doing this way will take about a hour to duplicate a database with about
> 50,000 records.  Is there any other ways that you can help me to speed up
> the process?
>
> All of your help will be greatly appreciated.
>
> Dan
--
Hunter Matthews                          Unix / Network Administrator
Office: BioScience 145/244               Duke Univ. Biology Department
Key: F0F88438 / FFB5 34C0 B350 99A4 BB02  9779 A5DB 8B09 F0F8 8438
Never take candy from strangers. Especially on the internet.