Обсуждение: [GENERAL] pg_rewind copy so much data

On Wed, Sep 13, 2017 at 12:41 PM, Hung Phan <hungphan227@gmail.com> wrote:
> I have tested pg_rewind (ver 9.5) with the following scenario:
>
> - one master and one slave (total size of each server is more than 4GB)
> - set wal_log_hint=on and restart both
> - stop master, promote slave
> - start old master again (now two servers have diverged)
> - stop old master, run pg_rewind with progress option

That's a good flow. Don't forget to run a manual checkpoint after
promotion to update the control file of the promoted standby so as
pg_rewind is able to identify the timeline difference between the
source and the target servers.

> The pg_rewind ran successfully but I saw it copied more than 4GB (4265891 kB
> copied). So I wonder there was very minor difference between two servers but
> why did pg_rewind copy almost all data of new master?

Without knowing exactly the list of things that have been registered
as things to copy from the active source to the target, it is hard to
give a conclusion. But my bet here is that you let the target server
online long enough that it had a bunch of block updated, causing more
relation blocks to be copied from the source because more efforts
would be needed to re-sync it. That's only an assumption without data
with clear numbers, numbers that could be found using the --debug
messages of pg_rewind.
-- 
Michael


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

On Wed, Sep 13, 2017 at 2:21 PM, Hung Phan <hungphan227@gmail.com> wrote:
> If I run pg_rewind with debug option, it just show additional bunch of files
> copied in directories like base or pg_tblspc. I claim that there is no data
> inserted of modified from the first step. The only difference between two
> server is caused by restarting old master.

That's exactly this kind of data that would allow to conclude where
the data comes from, and you are not showing it, so it is not possible
to make a clear conclusion. Are known the file paths, the offset
position in the file where a chunk of data is taken, and the chunk
size. If you see for example a bunch of 8kB blocks being copied, this
means that some extra load ran on the master before it was stopped,
and before running the rewind. Are you sure that you don't have large
log files for example?

I would be curious to know what is the version of Postgres 9.5 you are
using as well.
-- 
Michael


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

On Fri, Sep 15, 2017 at 2:57 PM, Hung Phan <hungphan227@gmail.com> wrote:
> [...]

Please do not top-post. This breaks the logic of the thread.

> I use ver 9.5.3.

You should update to the latest minor version available, there have
been quite a couple of bug fixes in Postgres since this 9.5.3.

> I have just run again and get the debug log. It is very long so I attach in mail
In this case the LSN where the promoted standby and the rewound node
diverged is clear:
servers diverged at WAL position 2/D69820C8 on timeline 12
rewinding from last common checkpoint at 2/D6982058 on timeline 12
The last segment on timeline 13 is 0000000D00000002000000E0, which may
be a recycled segment, still that's up to 160MB worth of data...

And from what I can see a lot of the data comes from WAL segments from
past timelines, close to 1.3GB. The rest is more or less completely
coming from relation files from a different tablespace than the
default, tables with OID 16665 and 16683 covering the largest part of
it. What is strange to begin with is that there are many segments from
past timelines. Those should not stick around.

Could you check if the relfilenodes of 16665 and 16683 exist on source
server but do *not* exist on the target server? When issuing a rewind,
a relation file that exists on both has no action taken on (see
process_source_file in filemap.c), and only a set of block are
registered. Based on what comes from your log file, the file is being
copied from the source to the target, not its blocks:
pg_tblspc/16386/PG_9.5_201510051/16387/16665 (COPY)
pg_tblspc/16386/PG_9.5_201510051/16387/16665.1 (COPY)
pg_tblspc/16386/PG_9.5_201510051/16387/16665_fsm (COPY)
And this leads to an increase of the data included in what is rewound.
So aren't you for example re-creating a new database after the standy
is promoted or something like that?
-- 
Michael


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

On Fri, Sep 29, 2017 at 1:06 PM, Hung Phan <hungphan227@gmail.com> wrote:
> I used tablespace to store data and it seems to be that pg_rewind copied
> everthing in the tablespace. Today I found an article posted by you (Michael
> Paquier) and you said that there was no tablespace support. If so, is there
> anyway to work around ?

I guess you mean that article:
http://paquier.xyz/postgresql-2/postgres-module-highlight-pg_rewind-to-recycle-a-postgres-master-into-a-slave/
This is from 2013 and this refers to the first iterations of the tool.
Tablespaces are now fully supported in the upstream version, as well
as in the version on github for 9.3 and 9.4. See this commit which
added support for tablespaces:
https://github.com/vmware/pg_rewind/commit/19fb09cdcac397048f7d723c037fe6a10299a278

FWIW, I use it daily, and pg_rewind is able to detect correctly page
deltas even on non-default tablespaces. Really.
-- 
Michael


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

On Fri, Sep 29, 2017 at 6:22 PM, Hung Phan <hungphan227@gmail.com> wrote:
> Thanks for your help. Do you have any more ideas about my case? I cannot
> find any reason for that. If pg_rewind just examines WAL for changed blocks
> from the common checkpoint, why did it copy all data ? As I understand, the
> information in WAL about changes cannot cover all data in the database.

The only thing I have in mind able to create this much amount of data
using this less WAL is a CREATE DATABASE using as template an existing
database. Based on the information you are giving here this is the
best guess I can do.
-- 
Michael


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