Backing up only certain tables... exporting to a delimited file reliable?

MarkB423

I am writing a PHP program for general distribution. I would like to have a utility to backup data from only the tables used by my program. Keep in mind that users may have several programs running from a single database (which is why I prefix all table names with a string that resembles the name of the program).

Would it be reliable to backup several tables of tens of thousands of records to a single file? Approximately how long should it take to write that many lines of data to a file, and would a single SELECT * statement per table work, or would I be required to LIMIT the SELECT to a certain number of records to avoid overloading anything?

Remember, compatibility is the key, so if an average server can only handle a SELECT of 10,000 records at a time, I would probably want to LIMIT it...

Thanks!

Sxooter

Most servers can handle selects into the millions.

The real issue is how to get the data back in, with INSERTS or a bulk copy. while bulk copies are faster, they are usually database specific methods, while the slower insert method tends to be more portable.

MarkB423

How would you do a bulk copy from a text file into a database table?

Also, how slow would it be to insert several tens of thousands of records into a database, assuming it was all done it one shot while looping through a file?

As long as PHP wouldn't timeout, the slowness of many inserts wouldn't quite matter... the database obviously wouldn't be in use during this procedure.

I mainly want the backup process to be used to restore a database from scratch in case something drastic happens.

Thanks for the comments!

Sxooter

I would use the backup tool for my database (pg_dump) to backup the data and then use the command line monitor (psql) to reload it.

If I have to import data from outside sources, I usually write a short php script that loops across each line in the input and inserts it into the database.

If you're working with postgresql, do your inserts inside a large transaction, like:

pg_query($conn,"begin");
<insert 100,000 rows here)
pg_query($conn,"commit");

Pig

some hosts limit the number of SELECT queries per hour.