I have an example table to demonstate a problem. create table myteble ( id serial primary key , metadata jsonb -- other fields -- ...... ) metadata is a jsonb column, and it might and most likely will contain some…
I've created a backup of a database on one computer postgres@machine1$ pg_dump mydb > mydb.dump.sql and then, from a second computer, having created a mydb database, I restore it from the dump postgres@machine2$ psql -d mydb -f mydb.dump.sql The problem…
PostgreSQL v12. Database cluster size: 12+ Tb (30+ databases 100-600Gb each). Server: 40 cores, 128Gb ram. For some reason, autovacuum stopped running. How to understand the reason? Yesterday, the workers were running every minute for each base (according to the…
I am having 2 servers, server (A,B). Can server A be publisher for table 1 and subscriber for table 2. And server B be publisher for table 2 and subscriber for table 1. What i reached so far, server A…
I have a strange situation in my PostgreSQL server. I have the below idle query that keeps running in all databases in the server. datid | 1029662 datname | ganar pid | 19022 usesysid | 10 usename | postgres application_name…
Consider the following example: There are 3 tables: clients (1) -> (n) orders (1) -> (n) order_updates. I want to update clients based on the corresponding order_updates with JOIN (which produces duplicates for clients data): UPDATE "clients" SET "clients"."status" =…
I have a Postgres DB with multiple databases, whose schemas are owned by individual service users. Now, I want to create a user which can access all those databases/schemas with atleast SELECT permissions. Is there any effective way of assigning…
I have a column name as Item_code which have the values like 'RAM-1','RAM-2', 'RAM-2'....'RAM-12'. I have to write procedure in postgreSQL which find maximum value from this column. CREATE OR REPLACE FUNCTION public.get_last_itemcode( item text) RETURNS text LANGUAGE 'plpgsql' COST…
I have a Postgres table with these columns: id int8 , user_id varchar , is_favorite boolean , join_time timestamptz I want to delete some rows in this table with some conditions: Keep a maximum of 10 rows for each user_id.…
I am running a simple query to get the max ID in a table: SELECT max(ID) FROM t WHERE m=345; Table (t) has 20 million records and 2000 distinct values for m. There is a primary key index on ID…