Hi all,
tl;dr:
is postgresql syncing filesystem caches after stop or should we do it
manually to be sure, that all data are on drives?
Long version:
First, we are using Postgresql 9.5 with some simple custom patches. OS is
centos 6, data are stored on ext4 fs.
We were moving a bunch of Postgresql instances (I'm talking about tens of
nodes with about thousand of DBs on each node) to a new HW. Because of some
technical/business/... reasons, we decided to use 'rsync': we prepared new
node, sync postgres' data during regular run, than stop DB on the old node,
do last rsync to move increments and start DB on the new node. Quite simple
and 99% of moved nodes and DBs were ok, but..
... but 1% had problems with a few of their DBs. We met (at least) two
errors:
1) toast data for `pg_statistic` table were corrupted ("missing chunk
number 0 for toast value....") - the solution was to delete records there
and run `analyze` to recreate it.
2) some tables disappeared:
2.1) `\d table_name` says, that the table doesn't exists ("Did not find any
relation named...")
2.2) `create table ..` says, that "type 'table_name' already exists"
2.3) sometimes I'm able to `drop type` the type, sometimes the drop fails
with "cache lookup failed for relation ..." :(
Is it possible, that Postgresql leave some data in memory after its stop?
Should we run `sync` after DB strop and before last rsync or somehow clean
FS caches to avoid problems like above? I'd expect, that postgresql will do
it during stopping processes, but maybe not.
The memory and FS cache are my biggest suspect, if you have idea what else
can be bad, feel free to suggest.
Thanks a lot,
- jj