That seems to be a misconfigured client application that explicitly tries to connect to a non-existent database 'admin' (via db=admin). Instead of adding that database, it seems more logical to fix the client configuration.
On Tue, 2 Apr 2019 at 09:53, Foo Bar <qubitreneg...@gmail.com> wrote: > Hello All, > > Ok, so maybe something helpful? On my master node I am seeing a bunch of: > > 2019-03-28 23:54:44 GMT [2611]: [1-1] > user=admin,db=admin,client=17210.10.37 172.10.10.37(57552) (0:3D000)FATAL: > database "admin" does not exist > 2019-03-28 23:54:46 GMT [2613]: [1-1] > user=admin,db=admin,client=172.10.10.18 172.10.10.18(56970) > (0:3D000)FATAL: database "admin" does not exist > > Which is accurate, as there is no admin database... I usually connect > with > > psql -h localhost -U admin postgres > > Should there be? Will this fix my issue with pgsql filling up the disk? > > Thanks, > - QBR > > On Mon, Mar 25, 2019 at 10:21 AM Foo Bar <qubitreneg...@gmail.com> wrote: > >> Hello All, >> >> Wow! Lots of awesome replies, Went away for the weekend thinking my >> email had been rejected and come back to a full inbox. Thanks for all the >> help! >> >> >> Postgres version? >> >> 9.6.11 >> >> Doh. Fairly important detail there. :) >> >> >> FYI, psql is the Postgres client program, Postgres(ql) is the server. >> >> "psql" is the name of a specific command line tool used to connect to >> a PostgreSQL database server, it is not the name of the database itself. >> The database is usually abbreviated "pgsql". >> >> Duly noted, thanks for the correction. >> >> >> It's the standby that has not seen any traffic? >> >> There's really no traffic. I built three nodes, connected them, created >> a test table and inserted some values, then left the cluster be for a >> couple weeks. >> >> >> And "restartpoint" is usually spelled as one work in technical >> discussions of it. Or at least, searching for it that way avoids finding >> things which mention each word separately in different senses. >> >> Ah ha. I had seen it that way but thought it was a typo. Thanks for the >> clarification! >> >> >> Are you sure it is the archive >> directory (/hab/svc/postgresql/data/archive) which is filling up, and not >> the live directory (pg_wal or pg_xlog)? This is often a point of confusion. >> >> Right before I sent the mail last week I deleted everything in >> /hab/svc/postgresql/data/archive, this morning I'm seeing: >> >> # du -h --max=1 /hab/svc/postgresql/data/ >> 198M /hab/svc/postgresql/data/pgdata >> 8.9G /hab/svc/postgresql/data/archive >> 9.1G /hab/svc/postgresql/data/ >> # du -hs /hab/svc/postgresql/data/pgdata/pg_xlog/ >> 177M /hab/svc/postgresql/data/pgdata/pg_xlog/ >> # ls -lah /hab/svc/postgresql/data/archive/ | wc -l >> 571 >> >> There is no pg_wal directory though (should there be?) >> >> # find /hab/svc/postgresql/ -name '*pg*wal*' >> # >> >> >> If the only reason you want an archive is for replication, then use >> streaming replication and do away with the archive completely >> >> To be honest, I thought it was required for streaming replication based >> on the guides linked above. >> >> >> There are reasons other than replication that one might want to keep a >> WAL archive, but those reasons don't seem to apply to you >> >> Like backup maybe? A wholly other topic, we recently had a power outage >> and I lost a pgsql node... having an archive would allow me to "replay" any >> transactions? >> >> >> What needs to be determined here is why the standby never consumed >> the WAL's from the master? >> Ok, so it the standby that's the problem. >> >> >> Do you still have the logs from the standby and do they show anything >> relevant? >> >> Sure, what am I looking for? I see a bunch of entries like: >> >> 2019-03-08 17:06:11 GMT [1813]: [815-1] user=,db=,client= (0:00000)LOG: >> restartpoint complete: wrote 22 buffers (0.0%); 0 transaction log file(s) >> added, 0 removed, 1 recycled; write=2.211 s, sync=0.062 s, total=2.281 s; >> sync files=18, longest=0.062 s, average=0.003 s; distance=16383 kB, >> estimate=16383 kB >> 2019-03-08 17:06:11 GMT [1813]: [816-1] user=,db=,client= (0:00000)LOG: >> recovery restart point at 0/8D000028 >> >> On the 15th, around when I think I filled the disk, I see a bunch of: >> >> cp: cannot stat '/00000002.history': No such file or directory >> cp: cannot stat '/000000010000000400000049': No such file or directory >> 2019-03-15 23:59:49 GMT [16691]: [1-1] user=,db=,client= >> (0:XX000)FATAL: could not connect to the primary server: could not connect >> to server: Connection refused >> Is the server running on host "172.16.10.23" and accepting >> TCP/IP connections on port 5432? >> >> Which makes sense since the pgsql service was down. >> >> This appears to be when I recovered the master on Thursday: >> >> cp: cannot stat '/00000002.history': No such file or directory >> cp: cannot stat '/00000001000000040000004D': No such file or directory >> 2019-03-21 17:37:31 GMT [31338]: [1-1] user=,db=,client= >> (0:XX000)FATAL: could not connect to the primary server: could not connect >> to server: Connection refused >> Is the server running on host "172.16.10.23" and accepting >> TCP/IP connections on port 5432? >> >> cp: cannot stat '/00000002.history': No such file or directory >> cp: cannot stat '/00000001000000040000004D': No such file or directory >> 2019-03-21 17:37:36 GMT [31343]: [1-1] user=,db=,client= (0:00000)LOG: >> started streaming WAL from primary at 4/4D000000 on timeline 1 >> 2019-03-21 17:37:47 GMT [30711]: [3151-1] user=,db=,client= >> (0:00000)LOG: restartpoint starting: time >> 2019-03-21 17:37:47 GMT [30711]: [3152-1] user=,db=,client= >> (0:00000)LOG: restartpoint complete: wrote 0 buffers (0.0%); 0 transaction >> log file(s) added, 0 removed, 1 recycled; write=0.003 s, sync=0.000 s, >> total=0.007 s; sync files=0, longest=0.000 s, average=0.000 s; >> distance=16384 kB, estimate=16384 kB >> >> Then there's a bunch of the same entries where about the only thing >> (other than the timestamp) that seems to change is the index in: >> >> 2019-03-23 23:33:40 GMT [30711]: [5094-1] user=,db=,client= >> (0:00000)LOG: recovery restart point at 5/90000140 >> 2019-03-23 23:38:40 GMT [30711]: [5097-1] user=,db=,client= >> (0:00000)LOG: recovery restart point at 5/91000028 >> 2019-03-23 23:43:40 GMT [30711]: [5100-1] user=,db=,client= >> (0:00000)LOG: recovery restart point at 5/91000108 >> >> I see effectively the same logs on the other hotstandby pgsql node. >> >> This is my config on my standby node: >> >> listen_addresses = '0.0.0.0' >> port = 5432 >> max_connections = 100 >> external_pid_file = '/hab/svc/postgresql/var/postgresql.pid' >> authentication_timeout = 1min >> max_files_per_process = 1000 >> max_locks_per_transaction = 64 >> logging_collector = on >> log_directory = '/hab/svc/postgresql/var/pg_log' >> log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log' >> log_line_prefix = '%t [%p]: [%l-1] user=%u,db=%d,client=%h %r (%x:%e)' >> log_min_messages = ERROR >> datestyle = 'iso, mdy' >> default_text_search_config = 'pg_catalog.english' >> data_directory = '/hab/svc/postgresql/data/pgdata' >> hba_file = '/hab/svc/postgresql/config/pg_hba.conf' >> wal_level = hot_standby >> wal_log_hints = 'on' >> hot_standby = 'on' >> hot_standby_feedback = true >> max_wal_senders = 5 >> max_replication_slots = 5 >> checkpoint_completion_target = 0.9 >> max_wal_size = 1GB >> min_wal_size = 128MB >> wal_keep_segments = 8 >> log_checkpoints = on >> log_lock_waits = on >> log_temp_files = 0 >> log_autovacuum_min_duration = 0 >> track_activity_query_size = 2048 >> track_io_timing=on >> dynamic_shared_memory_type = 'none' >> archive_mode = 'on' >> archive_command = 'cp %p /hab/svc/postgresql/data/archive/%f' >> archive_timeout = '10min' >> max_standby_archive_delay = '30s' >> synchronous_commit = local >> include '/hab/svc/postgresql/config/postgresql.local.conf' >> >> Should I disable archive mode? Even though I'm not currently using it, >> it seems like there's a use-case for having it? And if I can configure out >> what causing the backup of archive files it should still be manageable? >> >> Thanks again for all the replies, while it hasn't solved the problem yet, >> this was incredibly helpful! Also, please don't hate me for munging all >> your replies into one reply... I thought it might be easier to follow than >> having three different branches...? >> >> Best Regards, >> - QBR >> >> On Fri, Mar 22, 2019 at 7:14 PM Michael Paquier <mich...@paquier.xyz> >> wrote: >> >>> On Fri, Mar 22, 2019 at 12:26:33PM -0400, Jeff Janes wrote: >>> > archive_cleanup_command is pretty much obsolete. The modern way to do >>> this >>> > is with streaming replication, using either replication slots or >>> > wal_keep_segments. If the only reason you want an archive is for >>> > replication, then use streaming replication and do away with the >>> archive >>> > completely. There are reasons other than replication that one might >>> want >>> > to keep a WAL archive, but those reasons don't seem to apply to you. >>> And >>> > if they did you almost certainly wouldn't want to run >>> > archive_cleanup_command on it. >>> >>> Personally, I still find archives also very valuable when a standby >>> creation takes a long time because of a large initial base backup and >>> that the partition dedicated to pg_wal is not large enough to support >>> the retention associated with a slot, and it is easier to have larger >>> retention policies in the archives. >>> -- >>> Michael >>> >> -- If you can't see the forest for the trees, Cut the trees and you'll see there is no forest.
