Re: Wal streaming

Hi, I’m using Postgres 17 and the latest versions of repmgr and barman 1. I’m replicating my database to another node using streaming replication. wal_level=replica, hot_standby=on. 2. I’ve got barman running on the primary node locally and have setup the barman config for streaming replicatio

Re: DROP ROLE blocked by pg_init_privs

immerrr again writes: > On Wed, Nov 26, 2025 at 1:08 AM Tom Lane wrote: >> The missing step here is >> DROP OWNED BY test_role; > It just makes me uneasy to run a command with such potential for data loss > in order to remove a role. Well, (a) if you try to DROP ROLE any role that still owns o

Re: DROP ROLE blocked by pg_init_privs

On Tue, Nov 25, 2025 at 7:36 PM immerrr again wrote: > > > I have written a couple of queries to manually clean up the system > tables pg_init_privs/pg_shdepends instead (see [1]) > > Sorry, wrong link [1]. Should have been > > 1. > https://www.postgresql.org/message-id/CAERznn-QWVpAvqnyF%3DrZfiu

Re: DROP ROLE blocked by pg_init_privs

> I have written a couple of queries to manually clean up the system tables pg_init_privs/pg_shdepends instead (see [1]) Sorry, wrong link [1]. Should have been 1. https://www.postgresql.org/message-id/CAERznn-QWVpAvqnyF%3DrZfiuxkeDG0tym_rY%2BRuEkSPWvzgi67Q%40mail.gmail.com >

Re: DROP ROLE blocked by pg_init_privs

Hi Tom, On Wed, Nov 26, 2025 at 1:08 AM Tom Lane wrote: > The missing step here is > > DROP OWNED BY test_role; > ... > See discussion here: > ... I had gone through that thread before posting, and yes, I mentioned "DROP OWNED BY" in my original message. Is this what everyone is doing these day

Re: DROP ROLE blocked by pg_init_privs

immerrr again writes: > Sorry, nvm, I forgot to drop the privileges in the repro. Can confirm, with > 16.9 it works as expected: > ... > The same script with postgres:17 image fails with the error I have been > describing: The missing step here is DROP OWNED BY test_role; You have to use that t

Re: DROP ROLE blocked by pg_init_privs

Sorry, nvm, I forgot to drop the privileges in the repro. Can confirm, with 16.9 it works as expected: [nix-shell:~]$ docker run --rm -ti -p :5432 -e POSTGRES_PASSWORD=pg_test_init_privs --name pg_test_init_privs -d postgres:16.9 564ab91604018ff903c428f6a8659207a4071c30dbb94d206b20cb4f5e8d1635

Re: DROP ROLE blocked by pg_init_privs

Hi, Thank you for replying. Great to know about pg_read_all_data, will have a look at that. Re: it works, not sure, can't make it work on my side. Here's a full repro: [nix-shell:~]$ docker run --rm -ti -p :5432 -e POSTGRES_PASSWORD=pg_test_init_privs --name pg_test_init_privs -d postgres:16

Re: DROP ROLE blocked by pg_init_privs

Hi On 24.11.2025 18:59, immerrr again wrote: First time trying to configure a PG cluster by the book, I want to create a role with read permissions on all current and future tables in the current db. It looks smth like this CREATE ROLE test_role; GRANT SELECT ON ALL TABLES IN SCHEMA public TO t

Re: Restore Windows dump to Linux (locale issue)

On 11/25/25 14:03, Ertan Küçükoglu wrote: Adrian Klaver >, 26 Kas 2025 Çar, 00:20 tarihinde şunu yazdı: On 11/25/25 13:06, Ertan Küçükoglu wrote: > > Hello, > I am not understanding the below. > I manually created a database on th

Re: Restore Windows dump to Linux (locale issue)

Adrian Klaver , 26 Kas 2025 Çar, 00:20 tarihinde şunu yazdı: > On 11/25/25 13:06, Ertan Küçükoglu wrote: > > > > > Hello, > > > > I am not understanding the below. > > > I manually created a database on the Linux system. > > Took a database backup and restored it on the Linux system. > > That is w

Re: Schema design: user account deletion vs. keeping family tree data

On Tue, Nov 25, 2025 at 4:08 PM Jan Claeys wrote: [snip] > Genealogy is messy, and you will have to be able to store all sorts of > data you didn’t expect at first thought (see also the website about > names Rob Sargent linked to). > You also seem to make assumptions about relations being 1:1 or

Re: Restore Windows dump to Linux (locale issue)

On 11/25/25 13:06, Ertan Küçükoglu wrote: Hello, I am not understanding the below. I manually created a database on the Linux system. Took a database backup and restored it on the Linux system. That is what started this thread, an issue with a backup from a Windows machine. So where d

Re: Selecting all variations of job title in a list

On Tuesday, November 25, 2025, Rich Shepard wrote: > Companies can have slightly different titles for the same job; for example > (using abbreviations). 'Asst Gen Mgr.', 'Env Mgr,', 'Gen Mgr,'. 'Mgr,', > 'Plant Mgr.' > Suggest encoding that knowledge in a table then using normal joins and filter

Re: Schema design: user account deletion vs. keeping family tree data

On Mon, 2025-11-24 at 12:27 +0100, Christoph Pieper wrote: > I’m designing a schema for a family‑tree web app on PostgreSQL. Users > register accounts and can create one or more family trees. Each tree > consists of persons (the user themself, relatives, ancestors). Many > persons in a tree will ne

Re: Restore Windows dump to Linux (locale issue)

Brent Wood , 24 Kas 2025 Pzt, 01:42 tarihinde şunu yazdı: > If the dump file is too big to edit conventionally, piping it through a > stream editor like sed to make the required changes works nicely. > It can be done on the fly in the restore process if required. > > Brent Wood > > Principal Techn

Re: Selecting all variations of job title in a list

On 11/25/25 12:53, Rich Shepard wrote: On Tue, 25 Nov 2025, Adrian Klaver wrote: You will need to be clearer about what you want. If it is to fetch titles with Mgr in the name then it would be simple: select * from people_table where title ilike '%mgr%'; If it is something more selective you

Re: Selecting all variations of job title in a list

On Tue, 25 Nov 2025, Adrian Klaver wrote: You will need to be clearer about what you want. If it is to fetch titles with Mgr in the name then it would be simple: select * from people_table where title ilike '%mgr%'; If it is something more selective you will need to provide a more detailed e

Re: Selecting all variations of job title in a list

On 11/25/25 11:08, Rich Shepard wrote: On Tue, 25 Nov 2025, Adrian Klaver wrote: Something like?: select 'Env mgr.' ilike  ANY(ARRAY['Asst Gen Mgr.', 'Env Mgr.', 'Gen Mgr.', 'Mgr.','Plant Mgr.']); Adrian, Hadn't thought of this. Your, and Ron's, answers taught me that the answer to my que

Re: Selecting all variations of job title in a list

On Tue, 25 Nov 2025, Ron Johnson wrote: Failed clarity on my part. The "OR clauses" are within the regex string. Ron, That's okay. Note that The Relational Way of doing this is for everyone to have title_code in their "person" record, and the "title" table will have, in addition to the tit

Re: Selecting all variations of job title in a list

On Tue, Nov 25, 2025 at 2:05 PM Rich Shepard wrote: > On Tue, 25 Nov 2025, Ron Johnson wrote: > > > Maybe regex_match() with a bunch of OR clauses. > > > > In bash, I'd do something like: > > grep -E ' ^Asst Gen Mgr.*|^Env Mgr.*|^Gen Mgr.*|^Mgr.*|^Plant Mgr..*' > > foo.txt > > Ron, > > I've not u

Commit LSN after Redo Done At LSN applied during recovery

Hi all, Migrating a large database from 13 to 17 using a logical replication. Postgres - SaaS (GCP). Subscriber - 17.7. Publisher - 13.22. Plan: 1. create publication [pub] 2. create logical replication slot [pub] 3. copy instance [pub -> sub] 4. extract the LSN of the "redo done" point from the

Re: Selecting all variations of job title in a list

On Tue, 25 Nov 2025, Adrian Klaver wrote: Something like?: select 'Env mgr.' ilike ANY(ARRAY['Asst Gen Mgr.', 'Env Mgr.', 'Gen Mgr.', 'Mgr.','Plant Mgr.']); Adrian, Hadn't thought of this. Your, and Ron's, answers taught me that the answer to my question is 'no, there is no shortcut.' :-)

Re: Selecting all variations of job title in a list

On Tue, 25 Nov 2025, Ron Johnson wrote: Maybe regex_match() with a bunch of OR clauses. In bash, I'd do something like: grep -E ' ^Asst Gen Mgr.*|^Env Mgr.*|^Gen Mgr.*|^Mgr.*|^Plant Mgr..*' foo.txt Ron, I've not used regex in postgres before, only in emacs and small languages. So I'll learn

Re: Indentation in a patch

> On 25 Nov 2025, at 19:14, Bernice Southey wrote: > .. for my first ever patch attempt. Awesome, we love to see new contributors! > I'm tempted to submit my patch without indenting the > if-block, and an explanation. That's perfectly fine, an incorrectly indentend diff is fine to submit if it

Indentation in a patch

Hi, I'm having a fight with git and indentation for my first ever patch attempt. I'm just adding an if statement around an existing 70-line code-block. It's a very simple few lines with --ignore-all-space. But the patch file is noisy because of 140 lines of +/- indentation diffs. I then realised

Re: Selecting all variations of job title in a list

On 11/25/25 08:33, Rich Shepard wrote: Companies can have slightly different titles for the same job; for example (using abbreviations). 'Asst Gen Mgr.', 'Env Mgr,', 'Gen Mgr,'. 'Mgr,', 'Plant Mgr.' I want to select all people table rows that contain these varieties. I know the 'like' operator u

Re: Selecting all variations of job title in a list

On Tue, Nov 25, 2025 at 11:33 AM Rich Shepard wrote: > Companies can have slightly different titles for the same job; for example > (using abbreviations). 'Asst Gen Mgr.', 'Env Mgr,', 'Gen Mgr,'. 'Mgr,', > 'Plant Mgr.' > > I want to select all people table rows that contain these varieties. I kno

Selecting all variations of job title in a list

Companies can have slightly different titles for the same job; for example (using abbreviations). 'Asst Gen Mgr.', 'Env Mgr,', 'Gen Mgr,'. 'Mgr,', 'Plant Mgr.' I want to select all people table rows that contain these varieties. I know the 'like' operator uses '%' as a wildcard, but is not accept

Re: Wal streaming

On 11/25/25 05:46, Andrew wrote: Hi, New to the list. If I’m Using streaming archiving with repmgr, and streaming archiving with barman, do I still need to maintain my archive command, and have processes in place to delete archived wals periodically? Or are they in effect defunct due to wal

Wal streaming

Hi, New to the list. If I’m Using streaming archiving with repmgr, and streaming archiving with barman, do I still need to maintain my archive command, and have processes in place to delete archived wals periodically? Or are they in effect defunct due to wal retention policies within Postgres?