*Note that PG10 was installed on this server. Prior to installing the new version, I stopped and disabled the PG10 service.* Here's the OS version of the server in question: I used the EDB installers linked from the main postgres site: https://www.postgresql.org/download/windows/ I was unable to install PG16 on a client's system due to the error: Failed to Load SQL Modules into Database Clusters which occurred near the end of installation. I have installed PG16 on Win10 and Win11 before (as well as some other Win Server OS's on client systems - but I'm not sure exactly what server versions) and have not run into this issue before. After I clicked through the error and the installation completed, the postgres service refused to start with Error 1067: The process terminated unexpectedly I tried a lot of suggestions I found online (many from this post ) Here's a full list of what I tried: - Rebooting the server - Installing as admin - Installing outside of the Program Files folder (C:\PostgreSQL) - Creating the PostgreSQL and data folders prior to install and giving full permissions to: User Group, Admin Group and the NETWORK SERVICE user. - Setting the service user to Local System, and also the local admin. - Using the default password ("postgres") in the installer - Explicitly setting the locale to English US in the installer I also tried to manually patch the install with some commands I found online: (initialize the DB cluster) initdb -D "C:\PostgreSQL\16\data" -U postgres -A password --pwfile=passwd.txt (simulate postgres service run) "C:\PostgreSQL\16\postgres.exe" -D "C:\PostgreSQL\16\data" The command to init the DB succeeded, but when I tried the second command (simulate service), I immediately got a generic Windows "PostgreSQL has stopped working" error. This is what the event viewer showed: > Faulting application name: postgres.exe, version: 16.0.4.0, time stamp: 0x66b37331 Faulting module name: ucrtbase.dll, version: 10.0.14393.7426, time stamp: 0x66f600f9 Exception code: 0xc0000409 Fault offset: 0x000000000006c9e8 Faulting process id: 0x1b38 Faulting application start time: 0x01dbfc1356b9793c Faulting application path: C:\Program Files\PostgreSQL\16\bin\postgres.exe Faulting module path: C:\Windows\System32\ucrtbase.dll Report Id: c29be876-cf53-4689-b6df-459d3bc7df35 Faulting package full name: Faulting package-relative application ID: Searching online suggested this might be due to issues with the server's runtime libraries, but this was starting to go way over my head. We tried installing PG15 and got the same errors. Eventually we tried going all the way back to PG12, and that worked. I know postgres made significant changes between 12 and 13. PG12 is EOL now, so it's not ideal to use that instead of a newer version, but this was the latest version we could get working. We will likely keep it as is for now, but I would appreciate any further insights or troubleshooting tips in case we decide to revisit the upgrade, or in case this happens with another client. Any help is much appreciated!

Running Copy test(id, name) From Program 'zcat /tmp/test.csv.gz' CSV Header; fails with:

SQL Error : ERROR: permission denied to COPY to or from an external program
Detail: Only roles with privileges of the "pg_execute_server_program" role may COPY to or from an external program.

even though it's been granted to the current user:

SELECT current_user;
-- report_mgr

 SELECT rolname FROM pg_roles WHERE
   pg_has_role(current_user, oid, 'member');

|rolname                  |
|-------------------------|
|pg_execute_server_program|
|report_mgr               |

Running on the official postgres:16 Docker image (PostgreSQL 16.2 (Debian 16.2-1.pgdg120+2) on x86_64-pc-linux-gnu, compiled by gcc (Debian 12.2.0-14) 12.2.0, 64-bit). The query works fine with the postgres user, both in psql and SQLModel/SQLAlchemy, but haven't been able to get it to work for report_mgr. The file is readable, though that's irrelevant as the command is never executed. The user was created like this:

CREATE OR REPLACE FUNCTION grant_on_schemas(
  role text,
  schemas text[],
  privilege text default 'select')
RETURNS SETOF text AS $$
DECLARE
  query text;
  schema text;
BEGIN
  query := format('GRANT CONNECT ON DATABASE %I TO %s;', (SELECT current_database()), role);
  -- Add query to result table as a new row, then execute
  Return Next query;
  Execute query;
 
  FOREACH schema IN ARRAY schemas LOOP
	  If privilege = 'select' Then
  	    -- Grant select
	    query := format('GRANT USAGE ON SCHEMA %I TO %s;', schema, role);
  		Return Next query;
  		Execute query;
		query := format('GRANT SELECT ON ALL TABLES IN SCHEMA %I TO %s;', schema, role);
		Return Next query;
		Execute query;
		query := format('ALTER DEFAULT PRIVILEGES IN SCHEMA %I GRANT SELECT ON TABLES TO %s;', schema, role);
		Return Next query;
		Execute query;
	  Elsif privilege = 'all' Then
	    -- Grant all
	    query := format('GRANT USAGE ON SCHEMA %I TO %s;', schema, role);
  		Return Next query;
  		Execute query;
		query := format('GRANT ALL ON ALL TABLES IN SCHEMA %I TO %s;', schema, role);
		Return Next query;
		Execute query;
		-- Apply them to new tables/views created by this admin account
		query := format('ALTER DEFAULT PRIVILEGES IN SCHEMA %I GRANT ALL ON TABLES TO %s;', schema, role);
		Return Next query;
		Execute query;
	    -- Other permissions: functions, procedures, routines, sequences
  	  Else
  	  	Return Next format('Error: privilege "%s" not found', privilege);
	  End If;
  END LOOP;
  Return;
END;
$$ LANGUAGE plpgsql;

Create Role report_mgr login password 'qwert';
Select grant_on_schemas('report_mgr', array['public'], 'all');
Grant pg_execute_server_program To report_mgr;

Any ideas will be appreciated.

I have an issue against which I seek some advise. I have set up logical replication between two databases. The database was/is syncing perfectly well. However - there is one huge table that started giving error due to FK constraint. I have eliminated the FK constraint error. After that I was trying to import the rows from the source to the destination. The import was successful. However the replication stopped working. To fix this I did the following 1. Dropped the logical replication slot. 2. Created the logical replication slot again. 3. Attached the replication slot to the subscription back again. After this the replication started working for other tables. However it still does not work for the huge table where we had the FK issue. The log does not say anything about something missing about the replication. I do not mind losing the data, but I would like this table to be replicated as well. I have also restarted the pgsql service. I see this in the log - > 2024-04-30 13:57:02.633 CEST LOG: logical replication apply worker for subscription "central_quality_data_rep_mirror_subscription" has started 2024-04-30 13:57:02.703 CEST LOG: C/8081E400 has been already streamed, forwarding to C/808206B0 2024-04-30 13:57:02.703 CEST STATEMENT: START_REPLICATION SLOT "central_quality_data_rep_mirror_subscription" LOGICAL C/8081E400 (proto_version '4', streaming 'parallel', origin 'any', publication_names '"central_quality_data_rep_publish_all"') 2024-04-30 13:57:02.705 CEST LOG: starting logical decoding for slot "central_quality_data_rep_mirror_subscription" 2024-04-30 13:57:02.705 CEST DETAIL: Streaming transactions committing after C/808206B0, reading WAL from C/808206B0. 2024-04-30 13:57:02.705 CEST STATEMENT: START_REPLICATION SLOT "central_quality_data_rep_mirror_subscription" LOGICAL C/8081E400 (proto_version '4', streaming 'parallel', origin 'any', publication_names '"central_quality_data_rep_publish_all"') 2024-04-30 13:57:02.705 CEST LOG: logical decoding found consistent point at C/808206B0 2024-04-30 13:57:02.705 CEST DETAIL: There are no running transactions. 2024-04-30 13:57:02.705 CEST STATEMENT: START_REPLICATION SLOT "central_quality_data_rep_mirror_subscription" LOGICAL C/8081E400 (proto_version '4', streaming 'parallel', origin 'any', publication_names '"central_quality_data_rep_publish_all"') 2024-04-30 14:02:02.473 CEST LOG: checkpoint starting: time 2024-04-30 14:02:03.814 CEST LOG: checkpoint complete: wrote 15 buffers (0.0%); 0 WAL file(s) added, 0 removed, 0 recycled; write=1.321 s, sync=0.010 s, total=1.342 s; sync files=14, longest=0.002 s, average=0.001 s; distance=49 kB, estimate=49 kB; lsn=C/8082CB78, redo lsn=C/8082CB40

I seem to keep getting a very bad query plan in PostgreSQL 16.2 when selecting a small number of rows from a large aggregated table via a join. Consider the following setup:

CREATE TABLE large_table (a integer, b integer, PRIMARY KEY (a, b));
INSERT INTO large_table SELECT * FROM generate_series(1,10000) a(a), generate_series(1,10) b(b);

CREATE TABLE small_table (a integer PRIMARY KEY);
INSERT INTO small_table VALUES (1),(1000),(7000),(15),(9999);

ANALYZE large_table, small_table;

Then

EXPLAIN 
SELECT
    a,
    array_agg
FROM
    (SELECT a, array_agg(b) FROM large_table GROUP BY a) _
    JOIN small_table USING (a);

results in the following plan:

Merge Join  (cost=0.42..3705.18 rows=250 width=36)
   Merge Cond: (large_table.a = small_table.a)
   ->  GroupAggregate  (cost=0.29..3665.43 rows=10011 width=36)
         Group Key: large_table.a
         ->  Index Only Scan using large_table_pkey on large_table  (cost=0.29..3040.29 rows=100000 width=8)
   ->  Index Only Scan using small_table_pkey on small_table  (cost=0.13..12.21 rows=5 width=4)

If I understand this correctly, PostgreSQL first aggregates the whole large_table before it picks out the five rows we actually care about, although the planner is clearly aware that doing so is very expensive and that small_table only contains five rows. Selecting the five rows explicitly, on the other hand, gives the expected result:

EXPLAIN 
SELECT
    a,
    array_agg
FROM
    (SELECT a, array_agg(b) FROM large_table GROUP BY a) _
WHERE a IN (1,1000,7000,15,9999);

GroupAggregate  (cost=0.29..23.21 rows=50 width=36)
   Group Key: large_table.a
   ->  Index Only Scan using large_table_pkey on large_table  (cost=0.29..22.34 rows=50 width=8)
         Index Cond: (a = ANY ('{1,1000,7000,15,9999}'::integer[]))

I've run into variations of this issue on several occasions lately and it is becoming a serious problem. Is there anything obvious I'm missing here? If not, is there a way around this problem, if listing the rows explicitly is not an option?

Imagine I have a table with balance transactions: | ID | Account ID | Amount | Currency | Date | | -- | ---------- | ------ | -------- | ---------- | | 1 | 1 | 100 | EUR | 2025-03-01 | | 2 | 1 | 200 | EUR | 2025-04-01 | | 3 | 1 | 300 | EUR | 2025-05-01 | And a table with known account balances: | ID | Account ID | Amount | Currency | Date | | -- | ---------- | ------ | -------- | ---------- | | 1 | 1 | 5100 | EUR | 2025-03-01 | | 2 | 1 | 5600 | EUR | 2025-05-01 | Is there any way to write a single query in PostgreSQL that would return the actual or estimated balances for a given date, grouped by currency? For example, for 2025-03-01 it should return: | Account ID | Amount | Currency | Is Estimated | | ---------- | ------ | -------- | ------------ | | 1 | 5100 | EUR | FALSE | For 2025-04-01 it should return: | Account ID | Amount | Currency | Is Estimated | | ---------- | ------ | -------- | ------------ | | 1 | 5300 | EUR | TRUE | For 2025-02-01 it should return: | Account ID | Amount | Currency | Is Estimated | | ---------- | ------ | -------- | ------------ | | 1 | 5000 | EUR | TRUE | I know it's possible to do in a function with several queries and storing intermediate results in variables, but is there a way to do it in a single query? Maybe CTE? Another approach I was considering is creating a view with daily account balance estimates starting from the very first known balance, but I consider it my last resort.

I am using PostgreSQL 16 upwards. I am currently trying to create an example for a relationship of type `G-|o-----|= 1 H. x CHAR(3), -- example for other attributes CONSTRAINT g_h_fk FOREIGN KEY (h, id) REFERENCES h (id, g) ); -- To table H, we add the foreign key reference constraint towards g. ALTER TABLE h ADD CONSTRAINT h_g_fk FOREIGN KEY (g) REFERENCES g (id);

Then I can insert data into the tables and read them back out as follows:

/* Insert into tables for G-|o-----|<-H relationship. */ -- Insert some rows into the table for entity type H. -- Not specifying g leave the references G as NULL for now. INSERT INTO h (y) VALUES ('AB'), ('CD'), ('EF'), ('GH'), ('IJ'); -- Insert into G and relate to H. We do this three times. WITH g_id AS (INSERT INTO g (h, x) VALUES (1, '123') RETURNING id) UPDATE h SET g = g_id.id FROM g_id WHERE h.id = 1; WITH g_id AS (INSERT INTO g (h, x) VALUES (3, '456') RETURNING id) UPDATE h SET g = g_id.id FROM g_id WHERE h.id = 3; WITH g_id AS (INSERT INTO g (h, x) VALUES (4, '789') RETURNING id) UPDATE h SET g = g_id.id FROM g_id WHERE h.id = 4; -- Link one H row to another G row. (We do this twice.) UPDATE h SET g = 3 WHERE id = 2; UPDATE h SET g = 3 WHERE id = 5; -- Combine the rows from G and H. SELECT g.id AS g_id, g.x, h.id AS h_id, h.y FROM h INNER JOIN g ON g.id = h.g; ``` This still requires the use of Common Table Expressions. However, by now, I am fairly confident that this is OK. Still, I am not 100% sure. I think both approaches do work and I could not find an error with either of them. But the two table method is probably more efficient and more elegant. @Akina was right.

Trying to upgrade a PostgreSQL 13 instance to version 16, I am running into trouble. Before this, I upgraded (in-place) without issue from 11 to 13, but now installing 16 causes issues. I do:

sudo apt install postgresql-16

and would expect subsequently to find config files in /etc/postgresql/16, but there is no such folder. Also, pg_lsclusters return only version 13. I can do sudo -u postgres /lib/postgresql/16/bin/initdb -D /var/lib/postgresql/16/main, which will work, but then there appears no service (as in systemctl status postgresql@16-main.service) and also still the cluster does not appear in the list. I am unable to find any clues as to what may be the cause, or how to proceed. I did an apt purge followed by a new install multiple times, but to no avail. Also, I have the same experience when installing PG 15. Any thoughs are welcome, thank you! For reference, this is the output of the installation:

user@server:/etc/postgresql$ sudo apt install postgresql-16
Reading package lists... Done
Building dependency tree... Done
Reading state information... Done
The following additional packages will be installed:
  postgresql-client-16
Suggested packages:
  postgresql-doc-16
The following NEW packages will be installed:
  postgresql-16 postgresql-client-16
0 upgraded, 2 newly installed, 0 to remove and 304 not upgraded.
Need to get 20.1 MB of archives.
After this operation, 69.5 MB of additional disk space will be used.
Do you want to continue? [Y/n] Y
Get:1 https://apt.postgresql.org/pub/repos/apt  jammy-pgdg/main amd64 postgresql-client-16 amd64 16.8-1.pgdg22.04+1 [1913 kB]
Get:2 https://apt.postgresql.org/pub/repos/apt  jammy-pgdg/main amd64 postgresql-16 amd64 16.8-1.pgdg22.04+1 [18.1 MB]
Fetched 20.1 MB in 2s (9535 kB/s)
Preconfiguring packages ...
Selecting previously unselected package postgresql-client-16.
(Reading database ... 202138 files and directories currently installed.)
Preparing to unpack .../postgresql-client-16_16.8-1.pgdg22.04+1_amd64.deb ...
Unpacking postgresql-client-16 (16.8-1.pgdg22.04+1) ...
Selecting previously unselected package postgresql-16.
Preparing to unpack .../postgresql-16_16.8-1.pgdg22.04+1_amd64.deb ...
Unpacking postgresql-16 (16.8-1.pgdg22.04+1) ...
Setting up postgresql-client-16 (16.8-1.pgdg22.04+1) ...
update-alternatives: using /usr/share/postgresql/16/man/man1/psql.1.gz to provide /usr/share/man/man1/psql.1.gz (psql.1.gz) in auto mode
Setting up postgresql-16 (16.8-1.pgdg22.04+1) ...
Processing triggers for postgresql-common (274.pgdg22.04+1) ...
Building PostgreSQL dictionaries from installed myspell/hunspell packages...
Removing obsolete dictionary files:
user@server:/etc/postgresql$ ls
13
user@server:/etc/postgresql$

Edit: I hope this is the right place for this question/issue. If not, please comment. I put it in stackexchange and it was closed within minutes without any comment. Happy with any input, also on my post!

Is PostgreSQL's any_value actually arbitrary when using ORDER BY? The documentation states: > any_value ( anyelement ) → same as input type > > Returns an arbitrary value from the non-null input values. Example queries:

-- nonsensical
WITH vals (v) AS ( VALUES (1),(3),(4),(3),(2) )
SELECT any_value(v ORDER BY v = 3 DESC) FROM vals;

-- more realistic example
SELECT any_value(username ORDER BY user_id DESC) AS a_username FROM users;

I did see that [version 17](https://www.postgresql.org/docs/17/functions-aggregate.html) documentation includes the following clause: > While all aggregates below accept an optional ORDER BY clause (as outlined in Section 4.2.7), the clause has only been added to aggregates whose output is affected by ordering. But it doesn't mention whether any_value's output is affected by ordering. Based on some trials, it appears to be deterministic. (probably because any_value just returns the first not null value and respects the ORDER BY) ----- As a follow up question, if it's not arbitrary, would it be "bad" to rely on this behavior?

I am using the Alpine docker image for Postgres 16 for this experiment. I have one container which is running and has some tables. I create a second container which is also running but has no tables. I take a basebackup with pg_basebackup of container 1 from inside container 2 (host = container 1). I successfully get the backup in container 2 and pg_verifybackup says it is verified. Then I rename the pgdata directory to pgdata_ini and basebackup directory to pgdata. Then I restart container 2. The steps followed are as follows: 1. pg_basebackup -h -U postgres -D basebackup 2. mv pgdata pgdata2 3. mv basebackup pgdata 4. chown -R postgres pgdata // to make postgres user of container be owner of backup dir 5. exit // come out of container 6. docker restart postgresdb2 All of this so far had worked multiple times without issues but suddenly it has started failing with the following error: WAL file is from different database system (the system identifiers don't match apparently). I have created fresh containers for the entire experiment but to no avail. Just to clarify, this is not with recovery from archived WALs or PITR, its a simple base backup. Any help would be greatly appreciated! **UPDATES:** If I skip the chown step (step 4), then it works fine. On hindsight, I realised I was not doing this when it was working but considering postgres is the owning user of the current data directory, I don't understand why so any insight on that would be helpful too!

baran@heaven:~$ pg_lsclusters
Ver Cluster Port Status Owner    Data directory              Log file
16  main    5432 online postgres /var/lib/postgresql/16/main /var/log/postgresql/postgresql-16-main.log
baran@heaven:~$ pg_dump --version
pg_dump (PostgreSQL) 16.6 (Ubuntu 16.6-0ubuntu0.24.04.1)
baran@heaven:~$ pg_dump --file "/home/baran/aa/aa.backup" --host "localhost" --port "5432" --username "postgres"  --format=c --large-objects "zkbiov2"
Password: 
baran@heaven:~$ file /home/baran/aa/aa.backup 
/home/baran/aa/aa.backup: PostgreSQL custom database dump - v1.15-0

I don't have pg 15 installed on my system. But when i try to do backup or restore file from pg16.6 server it give me error pg_restore: error: unsupported version (1.16) in file header I want to use postgres 16.6 only

I noticed that plainto_tsquery('english', 'the T-bird') produces the TSV 't-bird' & 'bird' instead of just 't-bird' which doubles the runtime for this particular query. How could I fix that without loosing the stop word removal and stemming ?

I'm more of a data engineer than administrator and my role/schema management knowledge is more or less frozen in time since 8.4. I'm aware that pg15 tightened public schema restrictions, and pg14 introduced "predefined roles " as well as scram-sha-256 password encryption by default. This is how I have been creating DBs and roles so far (typically 1:1 name/ownership).

-- as superuser role
CREATE USER test;
ALTER USER test WITH PASSWORD test;
CREATE DATABASE test WITH OWNER test;
\c test
REVOKE ALL ON DATABASE test FROM PUBLIC;
REVOKE ALL ON SCHEMA public FROM PUBLIC;
ALTER SCHEMA public OWNER TO test;

I also sometimes create read-only users, which typically involves granting fine-grained SELECT permission on specific objects. I am now using pg16 and trying to get up to speed on the recommended role management strategies. I have three questions: 1) How much of my pre-pg16 DB creation script (see above) is still relevant? Can it be simplified? 2) How can I create a new role with read-only access to public and read-write access to its own schema? For example editor has read-only access to test.public and read-write to test.editor 3) How can I create another role with read-only access to _all_ schemas in a given DB? I don't need anything further, such as row-level security, etc. Thanks in advance!

I recently upgraded PostgreSQL 14 to PostgreSQL 16 using --clone method. Both my data directories (old & new cluster) were on same volume. After few days, I started receiving below error. > 53100: could not extend file "base/160560/t64_168297303" with FileFallocate(): No space left on device Time when errors occur, I checked the volume free size using (df -hT) and found that more than 30% of space is available in the volume. In all cases, the query which gives this error is CREATE TEMP TABLE query. I also checked free iondes during same time (df -i) and found that there were enough available. I also removed old data directory but that did not resolve the error. I have one primary server and 1 replica server in async replication using patroni. PostgreSQL Version : PostgreSQL 16.6 (Ubuntu 16.6-1.pgdg22.04+1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 11.4.0-1ubuntu1~22.04) 11.4.0, 64-bit I was able to manually create a large file of 40 GB using fallocate successfully which pushed the used space on volume up to 96%. It is an EBS volume attached to EC2 which storing nothing else but PG 16 data directory which has symbolic link of pg_wal pointing to different volume. I kept volume size at 96% and waited for a period where the error usually occurs but it didn't. One thing to mention: after removing the old PG 14 data directory, the error has occurred only once. Earlier, there were many occurrences but not continuous.

Here is what I am doing: 1. I have a Postgres 16 docker container which is continuously archiving WAL files 2. After 00012 and 00013 were archived, I take a base-backup of the database using pg_basebackup which generates a 00014..backup file (these are example files and actual WALs are longer I know) 3. Now I copy over the base backup and the archived WALs into another Postgres 16 docker container which is newly created from same docker image (including version) 4. I make postgres user as owner for all these files 5. I remove the WALs from the pg_wal directory of base backup and update the restore_command of postgresql.conf (additionally I also undo the archive configs which were there on primary) 6. I remove 00012 and 00013 WALs as those are already there in base backup, so now archived WALs is only 00014 and the .backup file created 7. I create a recovery.signal file, which is empty and in the data directory 8. Then finally I change the name of the current pgdata to pgdata_ini and the backup directory as pgdata so that it acts as my data directory 9. Then I stop the container and start again but database startup fails due to invalid checkpoint record and could not find required checkpoint record Can someone point out what I am doing wrong here?

I am just asking here so that someone from Percona can check if possible. I have followed below documentation to configure pg_tde in RHEL 8 with PG version 16. https://percona.github.io/pg_tde/main/ After enabling and configuring pg_tde key provider and master key . pg_basebackup is getting failed with below error

[postgres@hostname postgres]$ pg_basebackup -D bkp_test -R -X stream -v
pg_basebackup: initiating base backup, waiting for checkpoint to complete
pg_basebackup: checkpoint completed
pg_basebackup: write-ahead log start point: 0/14000028 on timeline 1
pg_basebackup: starting background WAL receiver
pg_basebackup: created temporary replication slot "pg_basebackup_1391250"
WARNING:  aborting backup due to backend exiting before pg_backup_stop was called
pg_basebackup: error: COPY stream ended before last file was finished
pg_basebackup: removing contents of data directory "bkp_test"

In the logs I can see below error.

2024-10-18 07:55:21.132 EDT  LOG:  checkpoint starting: force wait
2024-10-18 07:55:21.136 EDT  LOG:  checkpoint complete: wrote 0 buffers (0.0%); 0 WAL file(s) added, 0 removed, 1 recycled; write=0.001 s, sync=0.001 s, total=0.004 s; sync files=0, longest=0.000 s, average=0.000 s; distance=16384 kB, estimate=27000 kB; lsn=0/14000060, redo lsn=0/14000028
2024-10-18 07:55:21.205 EDT  WARNING:  aborting backup due to backend exiting before pg_backup_stop was called
2024-10-18 07:55:21.205 EDT  ERROR:  invalid segment number 0 in file "pg_tde.map"
2024-10-18 07:55:21.205 EDT  STATEMENT:  BASE_BACKUP ( LABEL 'pg_basebackup base backup',  PROGRESS,  WAIT 0,  MANIFEST 'yes',  TARGET 'client')
2024-10-18 07:55:21.221 EDT  LOG:  unexpected EOF on standby connection
2024-10-18 07:55:21.221 EDT  STATEMENT:  START_REPLICATION SLOT "pg_basebackup_1391250" 0/14000000 TIMELINE 1

After loading shared library pg_tde on server level and configuring master key for specific DB level, I can see below files in base directory that is creating issue. but unable to understand .

./16505/pg_tde.map
./16505/pg_tde.dat

below are the databases with OID.

test=# select oid,datname from pg_database;
  oid  |  datname
-------+-----------
     5 | postgres
     1 | template1
     4 | template0
 16505 | test
(4 rows)

Thanks for your help and suggestion if any.

There is one primary and two replicas in postgresql 16. one replica is completely sync however, the other one keeps on lagging and after some hours its falls way behind and breaks replication. Primary COnfigurations maintenance_work_mem 4GB max_connections 300 max_parallel_workers 8 max_parallel_workers_per_gather 4 max_standby_archive_delay 25min max_standby_streaming_delay 25min max_wal_senders 50 max_wal_size 1GB min_wal_size 80MB shared_buffers 15GB wal_init_zero on wal_keep_size 15GB wal_level replica Moreover on Replicas below are the parameters: maintenance_work_mem 4GB max_connections 300 max_parallel_workers 8 max_parallel_workers_per_gather 4 max_standby_archive_delay 25min max_standby_streaming_delay 25min max_wal_senders 50 max_wal_size 1GB min_wal_size 80MB shared_buffers 15GB wal_init_zero on wal_keep_size 15GB wal_level replica hot_standby on I have been checking the replication lag by below query: SELECT CASE WHEN pg_last_wal_receive_lsn() = pg_last_wal_replay_lsn() THEN 0 ELSE EXTRACT(EPOCH FROM now() - pg_last_xact_replay_timestamp()) END AS log_delay; The replica keeps on lagging until the replication goes out. The error in logs is below: > LOG: invalid magic number 0000 in WAL segment > 0000000100000126000000BC, LSN 126/BC480000, offset 4718592

I'm new to postgres. Attempting to setup HA - streaming replication. postgresql.conf are similar on both primary and secondary. all the params in the conf files seems to be okay. archive_mode = on max_wal_senders = 10 archive_command = 'test ! -f /var/lib/postgresql/pg_archive/%f && cp %p /var/lib/postgresql/pg_archive/%f' primary_conninfo =configured restore_command ='/var/lib/postgresql/pg_archive/%f "%p"' exception on the secondary after the below command and start of the replica pg_basebackup -h x.x.x.x -p 5432 -U replicator -D /var/lib/postgresql/16/pgdata/ -Fp -Xs -R -v 2024-09-26 21:28:17.387 UTC LOG: listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432" 2024-09-26 21:28:17.398 UTC LOG: database system was interrupted; last known up at 2024-09-26 21:27:48 UTC sh: 1: /var/lib/postgresql/pg_archive/00000002.history: not found 2024-09-26 21:28:18.623 UTC FATAL: could not restore file "00000002.history" from archive: command not found 2024-09-26 21:28:18.627 UTC LOG: startup process (PID 7769) exited with exit code 1 2024-09-26 21:28:18.627 UTC LOG: aborting startup due to startup process failure 2024-09-26 21:28:18.629 UTC LOG: database system is shut down pg_ctl: could not start server Examine the log output. what do I miss? there is no file /var/lib/postgresql/pg_archive/00000002.history Thanks in advance!

Table name: employment_by_industry https://dbfiddle.uk/hkwDS2DS For each row, I want to select the column name that has the maximum value: ------------- What is the most succinct way to do that in PostgreSQL, with the column names only being listed once in the SQL query? For example, this is how it would be done in Oracle:

select objectid, max(col_name) keep (dense_rank first order by col_val desc) max_col_name
from employment_by_industry
unpivot (
  col_val
  for col_name in (
    agr_forest_fish, mining_quarry, mfg, electric, water_sew  --cols only listed once
  )
)
group by objected

https://dbfiddle.uk/DeaRoikf

I need to upsert data to a PostgreSQL database from a high traffic application which needs to be optimized for write performance. The different rows to upsert in a batch will have values for different columns. They are not full updates of all columns. And these would be upserts so the rows would need to be inserted or updated. My idea is to do an INSERT ON CONFLICT UPDATE where in the update part I'd use CASE WHEN conditions to write into the update part the data to update to for each row, based on the id's. I cannot simply set the columns to EXCLUDED.column_name as not all incoming rows will have all rows set. Some will have NULLs. I would like to know: 1. Is this an ok way of going about this (see example below)? 2. Are there performance issues in doing this the way it is shown below? Is the size of the query going to affect performance? Or using those CASE conditions in the update part? Other performance issues? 3. Is there a way to pass the parameters to the database just once instead of repeating them for the insert and update parts? Would using named parameters work for this or are they also passed twice? 4. If this is not the best way to do this, how would you go about it? Is there a standard way of doing a batch upsert with this kind of data with different columns provided for different rows to insert? **Example of what my idea for doing this is:** **Schema (PostgreSQL v16)**

CREATE TABLE employees (emp_id INTEGER, name TEXT, department TEXT,
PRIMARY KEY (emp_id));

--- **Query #1**

INSERT INTO employees VALUES (1, 'john', 'sales');

--- **Query #2**

INSERT INTO employees (emp_id, name, department) VALUES (1, DEFAULT, 'it'), 
(2, 'jack', 'sales') 
ON CONFLICT (emp_id) DO UPDATE SET name = CASE 
WHEN employees.emp_id=1 THEN employees.name 
WHEN employees.emp_id=2 THEN 'jack' END, 
department = CASE WHEN employees.emp_id=1 THEN 'it' 
WHEN employees.emp_id=2 THEN 'sales' END
WHERE employees.emp_id IN (1, 2);

**Expectation is that Query #2 inserted new employee jack and updated employee john's department to 'it'** **Query #3** SELECT * FROM employees; | emp_id | name | department | | ------ | ---- | ---------- | | 1 | john | it | | 2 | jack | sales | ---

Let's take as an example a dummy query that looks like this

SELECT a.*
FROM a
JOIN b on a.id = b.id

UNION

SELECT a.*
FROM a
JOIN c on a.id = c.id

The documentation states: > The planner will rewrite explicit JOIN constructs (except FULL JOINs) into lists of FROM items whenever a list of no more than this many items would result. This leads me to believe that join_collapse_limit will be evaluated for each sub-query in the query above, so it won't count as 2 joins for the entire query but as 1 join for each sub-query. However, it's unclear to me if that is actually the case and I can't find anything related in the docs. So, will it really be evaluated for each sub-query? If the answer to that is yes, then is there any exceptional case where it wouldn't like that? Edit: The explain output is the following

Unique
   ->  Sort
         Sort Key: a.id
         ->  Append
               ->  Nested Loop
                     Join Filter: (a.id = b.id)
                     ->  Seq Scan on a
                     ->  Seq Scan on b
               ->  Nested Loop
                     Join Filter: (a_1.id = c.id)
                     ->  Seq Scan on a a_1
                     ->  Seq Scan on c