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.

Answer 1

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.