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

Tom Lane wrote:

Kevin Bailey <kbailey@freewayprojects.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