Re: Can a whole server be restored over to another server in a single command?

Tom Lane wrote:
> Kevin Bailey <kbailey(at)freewayprojects(dot)com> writes:
>
>> Can a whole server be restored over to another server in a single command?
>>
>
> The output of pg_dumpall is really meant to be loaded into an empty
> server. Although it has a "--clean" switch, that's got serious
> limitations and I wouldn't recommend bothering with it.
>
> What exactly is the scenario you are trying to have here? Is this
> a poor man's substitute for replication?

Yes. Poor man's replication is what we're after! :o)

We have a server which is used by a client who provides web based
financial accounts applications. The application is always being
developed/extended and new databases are being added all the time.

What we're trying to provide is a secondary server which is synchronised
every night from the primary server. This would be used if there was a
major problem with the primary server. Effectively, this is a step up
from rebuilding a whole application server from backup data. I.e. the
Secondary(replacement) server is already built, online,
application/libraries installed and the data is up-to-date as per the
previous night.

(Possibly, if the budget is available in the future we may recommend a
clustering type solution).

> Do you want the destination
> server to include other stuff besides what is coming from the source?
>
>
The destination server (we call the Secondary server) only needs to be a
copy of the Primary (live) server.

The current plan is:

On a Postgresql database server the two lowest level objects seem to be
users(/roles) and databases. Now, the Secondary server already has the
DB user accounts created.

So what we're going to do is to concentrate on dropping then recreating
the databases on the Secondary server and then filling them with data
from the Primary databases.

We have a bash loop which can list the databases, owners and collation
on the Primary server.

for db_info in $(psql -U postgres -h ${remote} -l -A -t)
do

echo ${db_info}
done

Which gives us output something like

bean-21|sql-ledger|UTF8
bean-22|sql-ledger|UTF8
bean-23|sql-ledger|UTF8
bean-24|sql-ledger|UTF8

(We will put in code so the databases postgres, template0 and template1
will be ignored).

Now, within that loop we can get the three values for database, owner
and collation using IFS type stuff.

For each database in the list we will:

1. DROP the database on the Secondary server which was created in the
previous run - if it exists.
2. CREATE a new (empty) database with the relevant owner and collation.
3. Use pg_dump to extract data from the Primary DB and then feed this
into the newly created Secondary database.

(We will possibly be a bit cleverer and dump the databases to files
first and transfer them to try to make the process a bit more atomic).

Now, if a needed user account is missing from the Secondary we should
get an error message returned from the cron job and we'll manually
create the user.

We could be even cleverer and use psql to get a list of users from the
primary and make sure they are present on the secondary but in our case
there are only four users and they hardly ever change.

When I've finished the code I'll post it to the list. The other part
I'd like to figure out is how to get it to be as atomic as possible.

Cheers,

Kevin

> regards, tom lane
>
>