Recovery with Postgresql logical replication

I’m using Postgresql 13 on Debian 10.6 and learning about logical replication.

I’ve set up logical replication with one publisher and one subscriber of one table. I’m wondering what my options are for recovering data (or rolling back) when, for example, someone accidentally does something on the publisher side like updating all the data in the table with the wrong value, or even deleting everything from a table. With logical replication these unintentional changes will of course be applied to the subscriber.

I’ve relentlessly searched online but have had no luck finding out what my options are. I read about PITR but I’m thinking that’s suited more for physical replication, whereas I want to test rolling back changes on a specific database on a server.

PostgreSQL extended statistics

I have a 1 TB read-only database where performance is critical. It’s difficult to predict queries since they are dynamically generated by the users (the whole thing is basically a visualization platform atop a large collection of medical studies, and users select what they want to visualize). Queries can often be complex and involve 10+ joins. I recently learned about the extended statistics feature, but I find little information online about when best to use it (other than what’s in the documentation).

The DB is pretty well normalized, but makes extensive use of materialized views which are de-normalized. Is there any performance penalty or other issue with creating extended statistics (dependency and top n) for all pairwise columns? It would result in, say, 500 statistics on some 70 tables. Time for analyze or inserts is not of relevance, only read performance. Also, is there a tool or code snippet to help me do this?

I’m using Postgresql 12 and it’s optimized as far as possible w.r.t. indexing.

Create cyclic pointers in postgresql

Figure this table:

id cat value updatedAt
1 cat1 v1 06/01/2021 00:00:01
2 cat1 v2 06/01/2021 00:00:02
3 cat1 v3 06/01/2021 00:00:03
4 cat2 v1 06/01/2021 00:01:01
5 cat2 v2 06/01/2021 00:01:02

I am looking for a request that will either insert or update depending the context. The idea is to simulate cyclic pointers in a postgres table.

Giving to the request the parameters cat=cat1 and value=v4. (Rolling update case, because there is already 3 records in cat1) the request should:

  • search for cat=cat1 records (3 matches)
  • 3 is the max count of record of each category (hardcoded), so
  • update record having id=1 with value=v4 and updatedAt=now() because it has the older createdAt value

Giving to the request the parameters cat=cat2 and value=v3, (Insert case, max count of cat=cat2 records is not reached) the request should:

  • search for cat=cat2 records (2 matches)
  • 2 is under the max count of record of each category (hardcoded), so
  • insert a record having id=6, cat=cat2, value=v3

It sounds weird to have one request performing INSERT or UPDATE. The need is to have cyclic pointers, "one request and no transaction" would be nice but maybe impossible?

Is it necessary to make a full backup after pg_upgrade with Postgresql?

pg_upgrade allows one to quickly upgrade big Postgresql databases, by taking advantage of the fact that the raw data does not need modification, it is only the configuration and metadata. However, I’m wondering how to handle the situation where the database was originally backed up using a full base backup, and from then on using archiving of WAL files. But how does that work with respect to upgrades? I tried to test it, so I configured postgres.conf of the new version to also WAL archive (to a different folder) and then upgraded using pg_upgrade. Both clusters produced some WAL files, however, it doesn’t seem to be possible to restore the database by using the original base backup combined with the WAL files (the new version refuses to start on such a directory, and the old version starts but only replays WAL files up to the point of the upgrade). So I’m wondering what the official procedure is regarding backup after using pg_upgrade. Does one have to make a fresh base backup or is there a way to only backup the changes made by the upgrade (WAL files or otherwise)? I can see there’s a procedure for upgrading standby servers but that is not the same scenario (although related).

Postgresql 12 and HAProxy – asking for guidance

I have two PG12 servers and they are in a Master-Slave relationship (replication).

What I want to do is have a virtual ip and link it to the master DB server being replicated to the slave one. I want the slave DB server to promote to master when the formal master dies. (failover)

I am trying to use HAProxy but have no idea. Examples from Google have other applications involved. I tried to use repmgr but I keep getting error during installation. (FATAL: no pg_hba.conf entry for host "__IP__", user "__USER__", database "__DB__", SSL off)

Can anyone please give me an advice or guide me with an example?

ADO.NET entity data model wizard crashes when creating a Postgresql data model

I am having trouble creating an ado.net data model from an existing Postgres database. I have used Npgsql and connected successfully but the wizard crashes without any error when I try to add a new entity data model. I have installed the Npgsql PostgreSQL Integration extension(Version 4.1.3.1) and the following packages:

<packages>   <package id="EntityFramework" version="6.2.0" targetFramework="net472" />   <package id="EntityFramework6.Npgsql" version="3.2.1.1" targetFramework="net472" />   <package id="Microsoft.Bcl.AsyncInterfaces" version="1.0.0" targetFramework="net472" />   <package id="Npgsql" version="4.1.3.1" targetFramework="net472" />   <package id="System.Buffers" version="4.5.0" targetFramework="net472" />   <package id="System.Memory" version="4.5.3" targetFramework="net472" />   <package id="System.Numerics.Vectors" version="4.5.0" targetFramework="net472" />   <package id="System.Runtime.CompilerServices.Unsafe" version="4.6.0" targetFramework="net472" />   <package id="System.Text.Encodings.Web" version="4.6.0" targetFramework="net472" />   <package id="System.Text.Json" version="4.6.0" targetFramework="net472" />   <package id="System.Threading.Tasks.Extensions" version="4.5.3" targetFramework="net472" />   <package id="System.ValueTuple" version="4.5.0" targetFramework="net472" /> </packages> 

The wizard crashes after clicking next on this window:

Entity Data Model wizard

Further technical details Npgsql version: Version 4.1.3.1 PostgreSQL version: 12.5 Operating system: Windows 10

Note:

  • I am using Npgsql -Version 4.1.3.1 instead of the latest stable version because the latest Npgsql PostgreSQL Integration extension in the VS marketplace is also of this version.
  • I am using EntityFramework6.Npgsql -Version 3.2.1.1 instead of the latest stable version because my project relies on EntityFramework -Version 6.2.0 and it is the latest version to have EntityFramework -Version 6.2.0 as the minimum dependency.
  • I have tried using all the latest stable versions of EntityFramework, Npgsql, and EntityFramework6.Npgsql and still had the same results

Thank you.

PostgreSQL. Consequently select results according with specific filters

I have a table with users.

User (that uses this programm) can select users by applying different filters. A filters must be applying immediatelly to reduce search time in final SELECT and no wasting time.

Currently my code looking like this (Python, not sure that this is working, not tested yet).

The question:

  1. How to applying a filters immediatelly as I get a user input?
  2. How to put/pass all actions related to filtering to DB?
if only_with_photo:     cursor.execute('SELECT tg_user_id FROM suers WHERE photo IS NOT NULL')     users_with_photo = cursor.fetchall() if only_with_country:     cursor.execute('SELECT tg_user_id FROM suers WHERE country IS NOT NULL')     users_with_country = cursor.fetchall() if only_with_city:     cursor.execute('SELECT tg_user_id FROM suers WHERE city IS NOT NULL')     users_with_city = cursor.fetchall()          cursor.execute('SELECT tg_user_id FROM users WHERE tg_user_id IN (users_with_photo) AND tg_user_id                              IN (users_with_country ) AND tg_user_id IN (users_with_city) ) 

Tasks: perform this queries inside psql without using Python or another language?

I tried:

  1. CASE sql = 'SELECT (CASE only_with_photo = 1 THEN (SELECT tg_user_id FROM users WHERE country IS NOT NULL)' --Subrequest returns more that one result
  2. Variables /set only_with_photo := (SELECT tg_user_id FROM users WHERE birthdate IS NOT NULL); --Syntax just now working

Can’t connect to postgresql database with SSL using GSSAPI

With postgresql 12.4, I’ve got this in pg_hba.conf:

hostssl all all 192.168.0.0 255.255.0.0 gss map=myrealm 

but whenever I try to connect with:

psql 'postgresql://hostname/database?sslmode=require' 

I get

2020-11-26 16:03:37.934 GMT [9585] FATAL:  no pg_hba.conf entry for host "192.168.215.220", user "username", database "database", SSL off 

If I replace hostssl with host I can connect (with GSSAPI).

How do I get psql to connect with SSL? I have ssl = on in my postgresql.conf and I have valid server.crt and server.key in the PGDATA directory.

Thanks