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.
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.
All connection are in LAN.
I want to know IP Adresess and if it is possible toogether with ProcessID of All active connection (to all databases). How to check active connections in PostgreSQL ?
Figure this table:
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?
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).
I have two PG12 servers and they are in a Master-Slave relationship (
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. (
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?
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 22.214.171.124) and the following packages:
<packages> <package id="EntityFramework" version="6.2.0" targetFramework="net472" /> <package id="EntityFramework6.Npgsql" version="126.96.36.199" targetFramework="net472" /> <package id="Microsoft.Bcl.AsyncInterfaces" version="1.0.0" targetFramework="net472" /> <package id="Npgsql" version="188.8.131.52" 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:
Further technical details Npgsql version: Version 184.108.40.206 PostgreSQL version: 12.5 Operating system: Windows 10
- I am using Npgsql -Version 220.127.116.11 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 18.104.22.168 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
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).
- How to applying a filters immediatelly as I get a user input?
- 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?
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
/set only_with_photo := (SELECT tg_user_id FROM users WHERE birthdate IS NOT NULL); --Syntax just now working
I want to run a script inside a transaction in postgresql. So I suround the sql with begin , commit statements. But I want to rollback on error. I dont see how to do that.
BEGIN; UPDATE public.tablename SET blah = ‘xxx’ WHERE thing= ‘123’;
With postgresql 12.4, I’ve got this in
hostssl all all 192.168.0.0 255.255.0.0 gss map=myrealm
but whenever I try to connect with:
2020-11-26 16:03:37.934 GMT  FATAL: no pg_hba.conf entry for host "192.168.215.220", user "username", database "database", SSL off
If I replace
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.key in the