Re: [INTERNET] Re: auto vacuum question

Alan Stange writes: > On 8/28/23 18:35, Jerry Sievers wrote: > >> Adrian Klaver writes: >> >>> On 8/28/23 13:06, Alan Stange wrote: >>> >>>> All, >>>> We recently changed the name of the superuser role in our database, >> My t

Re: auto vacuum question

Adrian Klaver writes: > On 8/28/23 13:06, Alan Stange wrote: > >> All, >> We recently changed the name of the superuser role in our database, My take on this, is that the *postmaster* user is perhaps the one that the OP cut privileges on, and thus the launcher is (now) spawning workers with less

ignore_system_indexes=on no help for missing/corrupt pg_class_oid_index?

Originally posted in the Admin list but trying here to get more eyes on it :-) Greetings admins! I found that the ignore_system_indexes flag apparently doesn't get me past this case of a corrupt index as seen below. Or am I doing something silly here? The particular DB is a scratch so we'

Re: pg_ctlcluster is not stopping cluster

Thorsten Glaser writes: > On Fri, 7 Apr 2023, Telium Technical Support wrote: > >>Notice: extra pg_ctl/postgres options given, bypassing systemctl for stop > >>it might be 13. Coincidentally, there is a postmaster.pid file in a >>directory OTHER than the data directory: >> >>/var/lib/postgresql/

Re: pg_dump failed with error code 255, but I don't see why

Ron writes: > On 9/22/22 09:34, Tom Lane wrote: > >> Ron writes: >>> v9.6.2.23 >>> I always run pg_dump with the --verbose option, and the bash script echos >>> $?, so there's a 25,664 line log file for me to examine which explicitly >>> shows the return code. >> A quick search of the 9.6 pg_dum

Re: Psycopg3 fails to resolve 'timezone localtime' on MacOS

> On May 5, 2022, at 8:54 PM, Jerry Sievers wrote: > > > >> On May 5, 2022, at 8:43 PM, Daniele Varrazzo >> wrote: >> >> On Fri, 6 May 2022 at 03:04, Jerry Sievers wrote: >>> >>> Has anyone run into This? >>> >

Re: Psycopg3 fails to resolve 'timezone localtime' on MacOS

> On May 5, 2022, at 8:43 PM, Daniele Varrazzo > wrote: > > On Fri, 6 May 2022 at 03:04, Jerry Sievers wrote: >> >> Has anyone run into This? >> >> Psycopg3 fails to resolve timezone localtime on MacOS (Catalina). > > That warning is typic

Re: Psycopg3 fails to resolve 'timezone localtime' on MacOS

> On May 5, 2022, at 8:29 PM, Tom Lane wrote: > > Jerry Sievers writes: >> Psycopg3 fails to resolve timezone localtime on MacOS (Catalina). > > I doubt Postgres will accept that zone name anywhere. It's > not a standard name as far as the tzdb data set is conc

Psycopg3 fails to resolve 'timezone localtime' on MacOS

Has anyone run into This? Psycopg3 fails to resolve timezone localtime on MacOS (Catalina). It falls back to UTC regardless of whether running with/without the tzdata package which I did try. There is a /etc/localtime symlink on this box pointed at the correct file in the tz data directory, whic

Re: Feedback about hybrid SAN snap and rsync'd approach for large systemcloning

Hi Stephen, and thanks! Please see below... > Greetings, > * Jerry Sievers (gsiever...@comcast.net) wrote: > Suppose we have a DB cluster with an additional tablespace and we are > able to make an atomic SAN snapshot of *only* the main cluster > volume... > The additional t

Feedback about hybrid SAN snap and rsync'd approach for large systemcloning

Suppose we have a DB cluster with an additional tablespace and we are able to make an atomic SAN snapshot of *only* the main cluster volume... The additional tablespace contains only UNLOGGED relations. We cannot snap that volume so we use rsync as follows... 1. pg_start_backup('foo'); make SAN

Re: Unkillable processes creating millions of tiny temp files

n the millions so it's hard to predict when cleanup will finish. As we did one night prior, a hard shutdown got the rogue jobs closed a lot more quickly and we noticed that on the recovery restart, Pg took about 15 minutes to clear >23M files from the temp area. I assume we'd experien

Re: multiple tables got corrupted

o whatever > verification you can do against other tables, you > should do as well. > > > You'll of course also want to check any kernel > logs or storage system logs to see if they can > give you a hint as to what happened, but they are > unlikely to actually give you something that will > help you fix the problem. > > > > -- Jerry Sievers Postgres DBA/Development Consulting e: postgres.consult...@comcast.net

Re: Obvious data mismatch in View2 which basically SELECT * from View1

stats_per_shift_filtered_u0206.wstate, > stats_per_shift_filtered_u0206.wline, > stats_per_shift_filtered_u0206.rcodes > FROM utlog.stats_per_shift_filtered_u0206; (as #View1 in this post) > > > It feels like the utlog.stats_per_shift_filtered_u0206 in > utlog.stats_per_shift_filtered definition is a different object from > utlog.stats_per_shift_filtered_u0206? > > I am totally out of clues. Any help would be appreciated. Thanks. > > > Regards, > > Ben > > > > > -- > Sent from my Android device with K-9 Mail. Please excuse my brevity. > > -- Jerry Sievers Postgres DBA/Development Consulting e: postgres.consult...@comcast.net

Re: Advancing the archiver position safely

le. Make certain you're *not* mucking with the WAL files themselves. > -- > -- Christophe Pettus >x...@thebuild.com > > > > -- Jerry Sievers Postgres DBA/Development Consulting e: postgres.consult...@comcast.net

Re: [HELP] query regarding replication

Andreas Kretschmer writes: > Am 09.06.20 um 10:44 schrieb Praveen Kumar K S: >> Thanks. Will this approach replicate DDL changes ? No. > > sure. > > > Regards, Andreas -- Jerry Sievers Postgres DBA/Development Consulting e: postgres.consult...@comcast.net

Re: Temporary table has become problematically persistent

nal broken database available for > debugging. Can I provide any additional debugging information? > > Thanks, > > Ricky > > -- Jerry Sievers Postgres DBA/Development Consulting e: postgres.consult...@comcast.net

Re: Promoting Hot standby after running select pg_xlog_replay_pause();

ictly prohibited. If > you have received this email in error, please notify the sender > immediately and delete the original. Neither this information block, > the typed name of the sender, nor anything else in this message is > intended to constitute an electronic signature unless a spe

Re: avoid WAL for refresh of materialized view

is is to not use mat view at all but instead materialize the output into an unlogged table that you trunc before every refresh. HTH > > > Kind regards, > > Alain Remund > > > -- Jerry Sievers Postgres DBA/Development Consulting e: postgres.consult...@comcast.net

Re: Temporary tablespaces on a RAM disk

s the WAL write :-) Er, but any standard 1-tablespace configuration is at risk of that, generally. FWIW > > Setting "work_mem" and "temp_buffers" high, you can use all the RAM > you have, and if it still is not enough, it spills to disk rather than die. > > Yours, > Laurenz Albe > > > > -- Jerry Sievers Postgres DBA/Development Consulting e: postgres.consult...@comcast.net

Re: pg_upgrade 9.6 to 12 without 9.6 binaries

nd it pg_upgrade needs to see the state of old > instance to recreate that state on the new instance. True. PgUpgrade will start/stop both versions separately perhaps a couple of times as part of the workflow. > >> >> Markus >> -- Jerry Sievers Postgres DBA/Development Consulting e: postgres.consult...@comcast.net

Re: Force WAL cleanup on running instance

files, perhaps just 1, though I've not run it lately to reverify. HTH > > kind regards > > Torsten -- Jerry Sievers Postgres DBA/Development Consulting e: postgres.consult...@comcast.net

Re: Postgres 12.1 : UPPER() in WHERE clause restarts server

rtner >B-1700 Dilbeek >Belgium > i...@arcict.comwww.arcict.com > tel. : +32 (0)2 466 50 00 fax. : +32 (0)2 466 88 33 > > > > -- Jerry Sievers Postgres DBA/Development Consulting e: postgres.consult...@comcast.net

Re: what causes xact_rollback for a database in pg_stat_database to increment?

have received this email in error, please notify > the sender by replying to this message and permanently delete this > e-mail, its attachments, and any copies of it immediately. You should > not retain, copy or use this e-mail or any attachment for any > purpose, nor disclose all o

Re: pg_terminate_backend not working

4 06:44:38.881326+00 | >  xyz | 2019-09-24 06:44:38.877561+00 | >  xyz | 2019-09-24 06:44:38.878336+00 |  > > what could be the possible reason for these connections and > pg_termiante_backend(pid) not working ? > > Thanks,  > Bhargav K   > > > > -- Jerry Sievers Postgres DBA/Development Consulting e: postgres.consult...@comcast.net

Re: pgbackrest - question about restoring cluster to a new cluster on same server

;re not quite sure how to handle > this but it seems a relatively minor issue, at least as far as > consistency is concerned. > > If you do have a split-brain situation where two primaries are archiving > on the same timeline then first-in wins. WAL from the losing primary > will be rejected. > > Regards, -- Jerry Sievers Postgres DBA/Development Consulting e: postgres.consult...@comcast.net

Re: pgbackrest - question about restoring cluster to a new cluster on same server

ctl start" processes > recovery.conf and applies the WAL files to the new cluster while the > prod cluster is writing new WAL files. There should be no issues *if* you have insured that the 2 server configurations do not overlap each other in any way. HTH > Does my question make sens

Re: pg_restore issues with intarray

Kevin Brannen writes: >>From: Jerry Sievers >> >>>Kevin Brannen writes: >>> >>> It feels like the restore is adding the intarray extension, which does >>> a CREATE OPERATOR FAMILY on its own, then later the restore does >>> CREATE OPERA

Re: pg_restore issues with intarray

the point where the > backup/dump happened. This is why I'm surprised I'm getting this error. > > It feels like the restore is adding the intarray extension, which does a > CREATE OPERATOR FAMILY on its own, then later the restore does CREATE OPERATOR > FAMILY on again causing the problem. Yet this doesn't happen on most of our > databases, just a few. It's maddening to me. > I've seen this sort of problem before. It was due to some legacy DBs where I work having a few missing extension membership registrations. pg_dump wants to include any such things in the output which may run afoul of same having been already created by the extension load. HTH -- Jerry Sievers Postgres DBA/Development Consulting e: postgres.consult...@comcast.net

No warning/error trying to vacuum other session's temp table

version - PostgreSQL 11.4 (Ubuntu 11.4-1.pgdg16.04+1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 5.4.0-6ubuntu1~16.04.11) 5.4.0 20160609, 64-b

Re: psql \copy hanging

; myview) TO STDOUT". > So where can things be hanging ? > My first guess (appart from db lock) was that the target file was > locked by the filesystem. But wouldn't psql know about it and fail > with an error ? > > Thanks for your help ! > > -- > Arnaud > > > -- Jerry Sievers Postgres DBA/Development Consulting e: postgres.consult...@comcast.net

Re: Statistics tables not being updated anymore

   > track_functions | none  > track_io_timing | off       > > Thanks > > -- > Angular momentum makes the world go 'round. > > -- Jerry Sievers Postgres DBA/Development Consulting e: postgres.consult...@comcast.net

Re: found xmin * from before relfrozenxid *

d by gcc (Debian 6.3.0-18+deb9u1) 6.3.0 > 20170516, 64-bit Your bug was fixed in minor version 10.5 :-) Plz go look at the release notes and then upgrade to latest minor release. HTH > > How can I solve theese errors? > > > Evaldas > > > > -- Jerry Sievers Postgres DBA/Development Consulting e: postgres.consult...@comcast.net

Re: Connection pooling for differing databases?

ions with > database names on another server, etc.  Is there a way to do > something like this? > > Thanks, > Arjun > > -- Jerry Sievers Postgres DBA/Development Consulting e: postgres.consult...@comcast.net

Re: Methods to quickly spin up copies of an existing databases

elling as physical size, or more precisely, *physical* copy time grow. HTH > > Regards, > Ken > > -- Jerry Sievers Postgres DBA/Development Consulting e: postgres.consult...@comcast.net

Re: Reclaiming space for dropped database

gt; them at all. (In fact, I think pg_upgrade intentionally makes the old > directory non-runnable to prevent that error.) So you might as well Yeah. IIRC, it renames control to pg_control.old to avoid accidental startup. > just "rm -rf ./10", not only its biggest subdirectory.

Re: Get LSN at which a cluster was promoted on previous timeline

ake a look at the *.history file in pg_xlog or pg_wal. > I want to know the state of the streamer post-promotion with respect > to the master by getting the LSN at which it was promoted. > > Thanks, > Jeremy > > -- Jerry Sievers Postgres DBA/Development Consulting e: postgres.consult...@comcast.net

Re: conditionally terminate psql script

pecifically to select all new >>> > rows that would conflict >>> > on a bulk insert, show them and stop >>> > >>> >> you need psql from PostgreSQL 10 and higher >>> >> there is a \if statement >>> >> Regards >>> >> Pavel >>> >> > >>> > Best regards >>> > Wolfgang >>> > >>> > >>> > >>> > > > > > > -- Jerry Sievers e: jerry.siev...@comcast.net p: 312.241.7800

Re: Logical replication hangs up.

---+ > | pg_walfile_name  | > +--+ > | 000104290069 | > +--+ > > /usr/lib/postgresql/10/bin/pg_waldump -s 429/69E9CC60 > 000104290069 | less > rmgr: Heap    len (rec/tot): 76/    76, tx:    5536495, lsn: > 429/69E9CC60, prev 429/69E9CC10, desc: DELETE off 81 KEYS_UPDATED , > blkref #0: rel 1663/16421/38572 blk 40 > rmgr: Heap    len (rec/tot): 76/    76, tx:    5536495, lsn: > 429/69E9CCB0, prev 429/69E9CC60, desc: DELETE off 82 KEYS_UPDATED , > blkref #0: rel 1663/16421/38572 blk 40 > rmgr: Heap    len (rec/tot): 76/    76, tx:    5536495, lsn: > 429/69E9CD00, prev 429/69E9CCB0, desc: DELETE off 83 KEYS_UPDATED , > blkref #0: rel 1663/16421/38572 blk 40 > > The relation 38572 from list above is an ordinary non replicated > table. > > Any help or advice how to diagnose/get it working is highly > appreciated. > > Kind regards Ales Zeleny > > -- Jerry Sievers Postgres DBA/Development Consulting e: postgres.consult...@comcast.net p: 312.241.7800

Re: Oracle vs PG

ctually ready to pay the cost of extra round trips to the > server to be able to get this property, even if that costs performance. > You can issue a query through the driver and rollback at will this way > to the previous state of the transaction. Yep and it's fun watching the txid co

Re: Oracle vs PG

s of times over when/if they can avoid paying what it likely an absurd cost to licence the $big-commercial-db. FWIW -- Jerry Sievers Postgres DBA/Development Consulting e: postgres.consult...@comcast.net p: 312.241.7800

Re: Vacuum not deleting tuples when lockless

at behavior is > present in 9.2.x. Oops, didn't notice OP was on 9.2! Presume none, since I don't think we got rep slots till 9.4 :-) > > regards, tom lane > > -- Jerry Sievers Postgres DBA/Development Consulting e: postgres.consult...@comcast.net p: 312.241.7800

Re: Vacuum not deleting tuples when lockless

* Old open prepared xacts. * Inactive replication slots. * Standbys confi'd with large vacuum clean up delay and feedback enabled. HTH > given table to solve our performance issues. > > Important thing to clarify is that we are using postgresql 9.2.24 > > Thanks in advance! > > Best, > Martín > > -- Jerry Sievers Postgres DBA/Development Consulting e: postgres.consult...@comcast.net p: 312.241.7800

Re: Duplicating data folder without tablespace, for read access

s when we snapshot large DBs on the SAN which does *not* capture our NVMe temp tablespace... I'd say find the objects supposed to be in the missing tablespace by catalog scraping and drop them. You'll see warnings or whatever in the logs that file $foo not found but the objects are the

Re: Space Stalker in SQL Output

to assign a value to a shell script variable: > > IDz=`psql -d proddb -U produser -h 10.9.999.99 -p 99900 -t < > last_id.sql` Get in the habit of including -A which gets rid of alignment padding in psql output. As in... shellvar=`psql -Atqc 'select froboz;'` $db HTH --

Re: pg_upgrade 10.2

Murthy Nunna writes: BTW, this message was and remained cross-posted to 3 groups which is considered bad style around here and I was negligent too in the previous reply which also went out to all of them. Please take note. Thank -- Jerry Sievers Postgres DBA/Development Consulting e

Re: pg_upgrade 10.2

ight never have seen your $silly-dash since possibly your shell or terminal driver swallowed it. > > Thanks for pointing that out. I will redo my upgrade. > > -r -v -k -c --- good flags no utf8 > -r -v -k –c --- bad flags.... > > > > > -Original Message- >

Re: pg_upgrade 10.2

g that stands out is that in your original > post you had: > > -p 5432 > > and above you have: > > -p 5433 > > Not sure if that makes a difference. > > The only suggestion I have at the moment is to move -c from the end of the > line to somewhere earlier

Re: Slow planning time for simple query

elate with the problem going away. Does that sound like a plausible explanation for the observed slow planning times? Thx > We made a fix last year to improve that: > https://git.postgresql.org/gitweb/?p=postgresql.git&a=commitdiff&h=3ca930fc3 > but it wasn't back-patched

Re: Which backend using which pg_temp_N schema?

Adrian Klaver writes: > On 06/06/2018 08:54 AM, Jerry Sievers wrote: > >> Adrian Klaver writes: >> >> >> Yep thanks... but IMO something that simply exposes whatever internal >> registry of temp schemas/PIDs (which I presume must exist) to DBA SQL >>

Re: Which backend using which pg_temp_N schema?

Tom Lane writes: > Jerry Sievers writes: > >> Yep thanks... but IMO something that simply exposes whatever internal >> registry of temp schemas/PIDs (which I presume must exist) > > Not really. There are a couple of ways that one could identify a > session's

Re: Which backend using which pg_temp_N schema?

Adrian Klaver writes: > On 06/05/2018 04:49 PM, Jerry Sievers wrote: > >> Adrian Klaver writes: >> >>> On 06/05/2018 02:53 PM, Jerry Sievers wrote: >>> >>>> Was just studying a legacy DB to learn about temp table activity. >>>> >&g

Re: Which backend using which pg_temp_N schema?

Adrian Klaver writes: > On 06/05/2018 02:53 PM, Jerry Sievers wrote: > >> Was just studying a legacy DB to learn about temp table activity. >> >> Felt like being able to tie temp schemas to live backends s/b useful but >> then didn't find a function/view for

Which backend using which pg_temp_N schema?

views. Am I missing it or does feature not exist? Thx -- Jerry Sievers Postgres DBA/Development Consulting e: postgres.consult...@comcast.net p: 312.241.7800

Re: pg_upgrade: when the --old-bindir requires "additional" $libdir/ plugins?

requisite dump... BOOM! > >> >> I don't remember anymore whether I'd set up the postgresql-update package >> to include the contrib modules for the old server version. If I didn't, >> it was an oversight :-(. >> >> regards, tom lane >> >> -- Jerry Sievers Postgres DBA/Development Consulting e: postgres.consult...@comcast.net p: 312.241.7800

Gratuitous use of savepoint considered silly, if not harmful

"COMMIT" 897 "BEGIN" 160 "DELETE" 83 "TRUNCATE TABLE" 6 "DROP TABLE" 2 "CREATE INDEX" 2 "CREATE TABLE AS" Anyway, I felt this was worth sharing :-) Thx -- Jerry Sievers Postgres DBA/Development Consulting e: postgres.consult...@comcast.net p: 312.241.7800

Re: best way to write large data-streams quickly?

n a transaction block, the block committed. HTH > > Thanks for everyones' help and input! > > Mark Moellering > > > > -- Jerry Sievers Postgres DBA/Development Consulting e: postgres.consult...@comcast.net p: 312.241.7800

Re: PgUpgrade bumped my XIDs by ~50M?

Bruce Momjian writes: > On Wed, Apr 4, 2018 at 08:29:06PM -0400, Bruce Momjian wrote: > >> On Wed, Apr 4, 2018 at 07:13:36PM -0500, Jerry Sievers wrote: >> > Bruce Momjian writes: >> > > Is it possible that pg_upgrade used 50M xids while upgrading? >> &

Re: PgUpgrade bumped my XIDs by ~50M?

Jerry Sievers writes: > Bruce Momjian writes: > >> On Wed, Apr 4, 2018 at 07:13:36PM -0500, Jerry Sievers wrote: >> >>> Bruce Momjian writes: >>> > Is it possible that pg_upgrade used 50M xids while upgrading? >>> >>> Hi Bruce. >&g

Re: PgUpgrade bumped my XIDs by ~50M?

Bruce Momjian writes: > On Wed, Apr 4, 2018 at 07:13:36PM -0500, Jerry Sievers wrote: > >> Bruce Momjian writes: >> > Is it possible that pg_upgrade used 50M xids while upgrading? >> >> Hi Bruce. >> >> Don't think so, as I did just snap

Re: PgUpgrade bumped my XIDs by ~50M?

Bruce Momjian writes: > On Wed, Apr 4, 2018 at 05:29:46PM -0500, Jerry Sievers wrote: > >> We have a large >20TB system just pg_upgraded from 9.5 to 9.6 as per the >> versions shown below. >> >> The system does <5M transactions/day based on sum(comm

PgUpgrade bumped my XIDs by ~50M?

-9.5 9.5.12-1.pgdg16.04+1 amd64object-relational SQL database, version 9.5 server ii postgresql-9.6 9.6.8-1.pgdg16.04+1 amd64object-relational SQL database, version 9.6 server Thanks -- Jerry Sievers Postgres

Re: ERROR could not access transaction/Could not open file pg_commit_ts

ick look to verify, just in terms of pg_commit_ts file names, between master and standbys to find no differences. Thanks -- Jerry Sievers Postgres DBA/Development Consulting e: postgres.consult...@comcast.net p: 312.241.7800