Обсуждение: 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.