how is it possible for the Slave to have more pg_xlog/ log files than the Master?
The whole point of archiving WAL on the master to some external location is to let the master then delete it to free space in its pg_xlog, while replicas might still need it.
A replica can have more archives in pg_xlog than the master, and older ones, if it's lagging behind the master due to failure to keep up with replay. However, with pg_standby that shouldn't happen - the archive might contain more xlogs, but the replica should only be reading them on-demand.
It's hard to be specific, because you've given a broad description of the issue rather than actual directory listings, and haven't explained the exact steps you followed to set up the replica. Or shown the exact log file output from the replica. So the best I can do is "it sounds like the replica setup is broken somehw".
to resync the servers in warm standby mode: do I have to do pg_basebackup again (to essentially copy Master's /data and /pg_xlog directory) to the Slave?
Assuming that here /data is the main datadir, containing global, base, pg_clog, etc, and that pg_xlog is the transaction logs from a different disk: Yes, that's right.
You must use the pg_basebackup command, though, or follow the instructions in the manual for correct file system level copies using pg_start_backup() and rsync/cp.
You also have to make sure you've stopped the replica first. Overwriting its datadir while it's running will make it quite upset.
Streaming replication vs warm standby
Hot vs warm standby is orthogonal to streaming vs log shipping replication.
What you're trying to do is use log shipping instead of streaming replication. It doesn't matter for this purpose if the replica is a hot standby or a warm standby, i.e. whether or not it's accepting queries.
Personally I recommend using both methods - use streaming, and fall back to log shipping if there's a problem with streaming. PostgreSQL does this automatically if both are configured.
The wal files are archived when they are no longer needed in the pg_xlog directory (usually when there are 16 segments and another one is needed - the one that is rolled over and reused is first archived). You can force the system to archive some files to verify manually if you want - to do this run the following:
select pg_start_backup('testing_archiving');
select pg_stop_backup();
Once pg_stop_backup() is finished, you should see a few files in the archive directory.
Postgres uses the archive status folder to record "notes" about archiving attempts and so on with regard to the WAL files.
Best Answer
If you suspect there's something wrong with our database (cluster), make a copy of your database directory before you continue. Just to be sure. On Ubuntu the default directory would be:
/var/lib/postgresql/9.3/main/- but your installation may differ.Actually, your command should work (in default Ubuntu installations), because the
servicecommand starts the postgres server process with appropriate privileges (as system user postgres)Translates to something like:
That starts all db clusters you may have. To start a particular one, use the wrapper
pg_ctlcluster:To get the status of all currently installed db clusters use
pg_lsclusters:You must be aware that the data directory is not the same as the config directory in default Debian or Ubuntu Postgres installations. For Postgres 9.3 that would be:
data dir:
/var/lib/postgresql/9.3/main/config dir:
/etc/postgresql/9.3/main/You can check the
/etc/postgresql/9.3/main/postgrsql.conffor the actual data dir. It's the line starting withThe error message indicates that Postgres tries to start with the default data dir, but it is not there or the privileges have been changed. Did you check?
Or are you running a non-default installation?