Re-sync slave server

On 06/06/2016 05:11 PM, Patrick B wrote:

Hi guys,

I'm using PostgreSQL 9.2

There is a backup server that is 4 days old replication by wal_files
(not streaming)

However, the server went down and I lost some wal_files.. and now the
replication is not working...

What is your set up?:

standby server
master --> | WAL archive --> standby_cluster

OR

wal storage standby server
master --> | WAL archive | --> standby

In other words where did you lose the WAL files?

More to the point are they still on the master or have they already been
recycled?

Question:

Do I need to sync all the data folder from my master....

Or just doing an incremental rsync would work?

Thanks
Patrick

--
Adrian Klaver
adrian.klaver@aklaver.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

My set up:

Master --> slave01 (streaming replication) --> slave02 (streaming
replication)
Master --> slave03 (wal_files 4 days old, not streaming replication)

The wal_files are stored into each server.
But when the slave03 was down, the wal_files weren't being copied into it.
We took too long to discover that, and now we lost some wal_files.. they've
been recycled...

So my question is:

Can I just do a RE-SYNC from slave01 to slave03 of the data folder?
Or do I have to sync all the database again?

I'm asking because the DB is 2.2TB, don't wanna do one step and discover
later that did not work and have to do all over again

Cheers
Patrick

On 06/06/2016 05:32 PM, Patrick B wrote:

My set up:

Master --> slave01 (streaming replication) --> slave02 (streaming
replication)
Master --> slave03 (wal_files 4 days old, not streaming replication)

The wal_files are stored into each server.
But when the slave03 was down, the wal_files weren't being copied into
it. We took too long to discover that, and now we lost some wal_files..
they've been recycled...

So my question is:

Can I just do a RE-SYNC from slave01 to slave03 of the data folder?

Are you archiving the WAL files on slave01?

Otherwise I would expect they would have been recycled there also.

Or do I have to sync all the database again?

I tend to believe so.

Have you taken a look at?:

https://www.postgresql.org/docs/9.2/static/app-pgbasebackup.html

Pay extra attention to the section:

-X method
--xlog-method=method

I'm asking because the DB is 2.2TB, don't wanna do one step and discover
later that did not work and have to do all over again

Cheers
Patrick

--
Adrian Klaver
adrian.klaver@aklaver.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On Mon, Jun 6, 2016 at 5:32 PM, Patrick B <patrickbakerbr@gmail.com> wrote:

My set up:

Master --> slave01 (streaming replication) --> slave02 (streaming
replication)
Master --> slave03 (wal_files 4 days old, not streaming replication)

The wal_files are stored into each server.
But when the slave03 was down, the wal_files weren't being copied into it.
We took too long to discover that, and now we lost some wal_files.. they've
been recycled...

So my question is:

Can I just do a RE-SYNC from slave01 to slave03 of the data folder?
Or do I have to sync all the database again?

If the master is successfully shipping WALs to slave03 now, re-syncing that
replica from slave01 (or slave02) should work fine. By "re-sync" I assume
you mean a full filesystem copy using pg_basebackup etc.

yep.. pg_basebackup.... the problem is that the servers are in a different
country.. and the DB is 2TB

the pg_basebackup will probably fail and I'll have to do a DUMP as I've
already done with another slave time ago

Anyway.. will do the pg_basebackup and see how it goes...

cheers guys
Patrick

2016-06-08 8:41 GMT+12:00 Patrick B <patrickbakerbr@gmail.com>:

One more question guys...

Does the pg_basebackup re-write the data? or do I have to have free space?

Like.. the DB is 2 TB.. do I have to have more 2TB? or it will use those
2TB that already are there?

cheers

On 06/07/2016 03:22 PM, Patrick B wrote:

One more question guys...

Does the pg_basebackup re-write the data? or do I have to have free space?

Like.. the DB is 2 TB.. do I have to have more 2TB? or it will use those
2TB that already are there?

I would suggest taking a tour of:

https://www.postgresql.org/docs/9.2/static/app-pgbasebackup.html

cheers

--
Adrian Klaver
adrian.klaver@aklaver.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

2016-06-08 11:03 GMT+12:00 Adrian Klaver <adrian.klaver@aklaver.com>:

On 06/07/2016 03:22 PM, Patrick B wrote:

One more question guys...

Does the pg_basebackup re-write the data? or do I have to have free space?

Like.. the DB is 2 TB.. do I have to have more 2TB? or it will use those
2TB that already are there?

I would suggest taking a tour of:

https://www.postgresql.org/docs/9.2/static/app-pgbasebackup.html

It does not say anything about that.. if it will use more space or if it
will replace the current data and the disk space will be the same

On 06/08/2016 01:47 PM, Patrick B wrote:

2016-06-08 11:03 GMT+12:00 Adrian Klaver <adrian.klaver@aklaver.com
<mailto:adrian.klaver@aklaver.com>>:

On 06/07/2016 03:22 PM, Patrick B wrote:

One more question guys...

Does the pg_basebackup re-write the data? or do I have to have
free space?

Like.. the DB is 2 TB.. do I have to have more 2TB? or it will
use those
2TB that already are there?

I would suggest taking a tour of:

https://www.postgresql.org/docs/9.2/static/app-pgbasebackup.html

It does not say anything about that.. if it will use more space or if it
will replace the current data and the disk space will be the same

https://www.postgresql.org/docs/9.2/static/app-pgbasebackup.html
"
-D directory
--pgdata=directory

Directory to write the output to. pg_basebackup will create the
directory and any parent directories if necessary. The directory may
already exist, but it is an error if the directory already exists and is
not empty.

When the backup is in tar mode, and the directory is specified as -
(dash), the tar file will be written to stdout.

This option is required.
"

So no it will not replace existing data. You will need to start with an
empty $DATADIR. If you do not want to duplicate the space empty the
existing $DATADIR. If you want to be double safe run the backup to
another directory and then copy it over later.

--
Adrian Klaver
adrian.klaver@aklaver.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general