pg_restore mostly idle on restoring a large number of tables

Hi, restoring a 1.5TB database with about 800k tables on i3.4xlarge AWS instace, PgSQL V12.15 on Ubuntu. Running pg_restore with -j 16, I noticed the pg_restore is busy for an hour or so with IO at 80%+ and then most of processes start idling and only a few doing some work, disk IO at 1-2%, pg_st

Re: Slave server sometimes locks up

Hi Thomas, thank you for your quick reply, much appreciated. 1. "dsa_area could not attach to segment": dsm.c, fixed in commit 6c0fb941. > 2. "cannot unpin a segment that is not pinned": dsm.c, fixed in commit > 0b55aaac. > > Yes, I found both entries in our logs, each once per incident. > The

Slave server sometimes locks up

PgSQL 10.7, Ubuntu 16.04 LTS Symptoms: - server accepts new queries until connections exhausted (all queries are SELECT) - queries are active, never end, but no disk IO - queries can't be killed with kill -TERM or pg_terminate_backend() - system load is minimal (vmstat shows 100% idle) - perf top

Re: Logical replication very slow

tzios < ach...@matrix.gatewaynet.com> wrote: > On 25/2/19 9:59 π.μ., Boris Sagadin wrote: > > > Doing an initial replica. > > postgres 119454 93.5 25.9 34613692 32649656 ? Rs 07:16 32:45 \_ > postgres: 10/main: bgworker: logical replication worker for subscrip

Re: Logical replication very slow

which in binary replication case is just hours, can be a big problem for us. Boris On Mon, Feb 25, 2019 at 8:08 AM Achilleas Mantzios < ach...@matrix.gatewaynet.com> wrote: > On 25/2/19 8:52 π.μ., Boris Sagadin wrote: > > Doing an initial replica and trying to find a bottleneck, Ubuntu 16

Logical replication very slow

Doing an initial replica and trying to find a bottleneck, Ubuntu 16.04, NVMe disks, PgSQL v10.7, AWS. With binary replication, DB is replicated at good speed, around 500MB/s. Trying LR now for a big table (about 1.4TB with 2 indexes) and the speed is only about 2MB/s. Checked disk util with iostat

Re: Postgres 10, slave not catching up with master

Yes, as stated, the lag went very much down after disabling wal compression, it's manageable at least. Network is 10GB. Lep pozdrav, *Boris Sagadin* InfoSplet, informacijske tehnologije, d.o.o. *www.infosplet.com* <http://www.infosplet.com/> | Tel: 0590 / 45 800, GSM: 041 / 337 848

Re: Postgres 10, slave not catching up with master

gt;> source: >> >> https://severalnines.com/blog/postgresql-streaming-replication-deep-dive >> >> El mar., 23 de oct. de 2018 a la(s) 11:28, Boris Sagadin ( >> bo...@infosplet.com) escribió: >> >>> Nothing special, just: >>> >>> standb

Re: Postgres 10, slave not catching up with master

?? > > El mar., 23 de oct. de 2018 a la(s) 00:28, Boris Sagadin ( > bo...@infosplet.com) escribió: > >> Yes, turning wal_compression off improves things. Slave that was >> mentioned unfortunately lagged too much before this setting was applied and >> was

Re: Postgres 10, slave not catching up with master

slowdown for write heavy databases. Maybe an option to increase wal_size beyond 16MB in v11 will help. In the meantime we'll solve this by splitting the DB to 2 or 3 clusters or maybe trying out some sharding solution like Citus. Boris On Sun, Oct 21, 2018 at 9:06 AM, Boris Sagadin

Postgres 10, slave not catching up with master

Hello, I have a database running on i3.8xlarge (256GB RAM, 32 CPU cores, 4x 1.9TB NVMe drive) AWS instance with about 5TB of disk space occupied, ext4, Ubuntu 16.04. Multi-tenant DB with about 4 tables, insert heavy. I started a new slave with identical HW specs, SR. DB started syncing from