PHP backup entire PostgreSQL database and then restore part of table

Question

I am currently backing up an entire database using pg_dump:

<?php

include_once("../db.php");

$password = getPGPassword();
$user = getPGUser();
$db = getPGDb();

putenv("PGPASSWORD=" . $password);
$dumpcmd = array("pg_dump", "-i", "-U", escapeshellarg($user), "-F", "c", "-b", "-v", "-f", escapeshellarg('/var/www/backups/backup-'.time().'.sql'), escapeshellarg($db));
exec( join(' ', $dumpcmd), $cmdout, $cmdresult );
putenv("PGPASSWORD");

?>

I know I can use psql to restore the entire database, but is there any way that I can selectively restore part of a table using a query? The simplest thing I can think of is creating a temporary database with psql, reading rows from the desired table, deleting conflicting rows based on primary serial key, and inserting into table.

Is there a better way to do this? I'll need full sql query functionality.

I've got a user interface and an admin interface. From the user interface you can make changes to the database. From the admin interface I'd like to be able to restore just one row using a PHP script so it doesn't have to be done manually/from the command line. — Max Hudson
– Max Hudson, Commented Sep 5, 2015 at 1:31
In that case if you make your dump in the 'plain' format your PHP script should be able to iterate through the file until it finds the primary key that interests you and insert only that row. — e4c5
– e4c5, Commented Sep 5, 2015 at 2:04
@maxhud Have you seen this one stackoverflow.com/questions/1109061/… — sitilge
– sitilge, Commented Sep 29, 2015 at 6:03
I'd heartily recommend you don't use PHP as it's another layer that could go wrong and with backups of data that is never a good thing. Could you use a cron job to do it regularly? — user3791372
– user3791372, Commented Oct 1, 2015 at 12:53

klin · Accepted Answer · 2015-10-02 19:03:19Z

1

+50

In my opinion, the easiest effective solution is:

install a backup server on another machine,
perform dump / restore on a regular basis or as needed,
connect main and backup servers using foreign data wrapper postgres_fdw.

In my practice, backup server is mandatory even for relatively small projects. Data replication can be accomplished in many ways. Dump / restore (possibly using cron) is one of the simplest, but configuring streaming replication is also not especially difficult.

If we must take into account the costs, the backup server can be just any pc or laptop with any operating system. I think this can be done easily even at home.

The benefits of having a backup server are manifold. Sometimes it is live-saving solution.

edited Oct 2, 2015 at 19:03

answered Oct 2, 2015 at 18:51

klin

123k15 gold badges241 silver badges263 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Max Hudson Over a year ago

Great answer. Thanks. Idk why I wasn't using a separate server.

Collectives™ on Stack Overflow

PHP backup entire PostgreSQL database and then restore part of table

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related