pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Clear FS caches after postgresql stops
2+ messages / 2 participants
[nested] [flat]

* Clear FS caches after postgresql stops
@ 2017-11-27 11:16 Jakub Jedelský <jakub.jedelsky@gmail.com>
  2017-11-27 13:22 ` Re: Clear FS caches after postgresql stops scott ribe <scott_ribe@elevated-dev.com>
  0 siblings, 1 reply; 2+ messages in thread

From: Jakub Jedelský @ 2017-11-27 11:16 UTC (permalink / raw)
  To: pgsql-admin

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

^ permalink  raw  reply  [nested|flat] 2+ messages in thread

* Re: Clear FS caches after postgresql stops
  2017-11-27 11:16 Clear FS caches after postgresql stops Jakub Jedelský <jakub.jedelsky@gmail.com>
@ 2017-11-27 13:22 ` scott ribe <scott_ribe@elevated-dev.com>
  0 siblings, 0 replies; 2+ messages in thread

From: scott ribe @ 2017-11-27 13:22 UTC (permalink / raw)
  To: Jakub Jedelský <jakub.jedelsky@gmail.com>; +Cc: pgsql-admin

On Nov 27, 2017, at 4:16 AM, Jakub Jedelský <jakub.jedelsky@gmail.com> wrote:
> 
> Is it possible, that Postgresql leave some data in memory after its stop?

Not unless you turned fsynch off in the conf, and you really should NOT be doing that for production.

Remember that rsync normally compares timestamp & size, and that timestamps are often only 1-second precision. Thus it's possible for changes to be skipped unless you use the -c flag.

--
Scott Ribe
https://www.linkedin.com/in/scottribe/
(303) 722-0567





^ permalink  raw  reply  [nested|flat] 2+ messages in thread


end of thread, other threads:[~2017-11-27 13:22 UTC | newest]

Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2017-11-27 11:16 Clear FS caches after postgresql stops Jakub Jedelský <jakub.jedelsky@gmail.com>
2017-11-27 13:22 ` scott ribe <scott_ribe@elevated-dev.com>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox