Questions tagged [postgresql-11]

PostgreSQL version 11

28 questions with no upvoted or accepted answers
Filter by
Sorted by
Tagged with
2
votes
0answers
38 views

How do I prevent PostgresQL users/roles from having access to any administrator functions?

When I create a user with CREATE ROLE user123 LOGIN on my system it is able to login and immediately perform \du and interact with pg_stat_activity, and probably many other things. I have ...
2
votes
1answer
135 views

PostgreSQL most efficient way to reference multiple tables

I am looking for the most efficient way to reference multiple tables in one table, when there is only one reference possible at a time. Which means tables A and B are referenced by table C, but both A ...
1
vote
1answer
51 views

Postgres Column Level Security

I have a schema snow within a database testdb. I'm trying to achieve column level security for my users. My users are boss, manager and intern. I set all of this table up as the superuser me. They are ...
1
vote
0answers
53 views

PostgreSQL logical replication and partition table

We have a large database and we want a copy of the database in our company for faster access. we are going to achieve this copy by logical replication on PostgreSQL 11. But we have a big problem. We ...
1
vote
1answer
23 views

PostgreSQL: Check the structure of foreign tables

How can I check if the structure of a foreign table was changed in the source database in order to know when to re import it. Foreign table was imported by foreign-data wrapper postgres_fdw (...
1
vote
0answers
31 views

Order of logical decoding output on Postgres 11

Given the following table in PostgreSQL 11.1, what is the order of logical decoding output rows? CREATE TABLE t1 ( t1_id INTEGER PRIMARY KEY ,name TEXT ) ; CREATE TABLE t2 ( t2_id INTEGER ...
1
vote
1answer
76 views

Efficient group by on sorted CSV using PostgreSQL FDW

I have a large CSV file sorted on the first column. Here is an extremely simplified version): a 7 a 4 b 6 c 3 c 9 c 2 I want to group by the first column and sum the second. How do I tell the ...
1
vote
0answers
28 views

Hosting postgres wal logs on a separate dedicated server with failover setup

a postgres novice here, so sorry if my questions don't sound meaningful. My postgres server setup goes as follows: Master(on server A), Upstream(on B, receiving wal from A), Downstream(on C, ...
0
votes
1answer
13 views

Options and best practices for Multiple Result Sets in Postgres11

I want to get two (small) result sets and have the benefit of precompilation and 1 round trip to the database. SQL Server allows a stored procedure to contain multiple select statements. SELECT u....
0
votes
0answers
11 views

PostgreSQL multi-pivot with keys as column names rather than categories?

I am trying to use crosstab() to generate a tricky pivot which is not really a true pivot. I am looking to get the key names as column names rather than the category. E.g. create table pivot (id ...
0
votes
1answer
22 views

How can I setup users/groups in PostgreSQL so that each user has privileges on objects created by other users in the same group?

I have created a group (role) called "employees" and I've created some users that are its member and that inherit its rights. I have a database owned by the group "employees". The goal: To setup ...
0
votes
0answers
17 views

Does Postgresql logical replication support hot standby?

Postgresql has two ways of replication Physical replication Logical replication with physical replication everything is send over and the servers must have the same major release. Which makes ...
0
votes
0answers
34 views

Postgresql: how to create a View combining regular Tables A, B, C and pivoted table D with a json field?

I have created a standard view, VIEW, that includes different fields from different tables: SELECT e.id AS "Employee Id", concat(e.first_name,' ',e.additional_family_name,' ',e.family_name) AS "...
0
votes
1answer
31 views

Unstructed data field: Query all values from array of objects by key

I have a table that has a unique ID, and a second 'collumn' named 'data' that contains simple key/value items like: "nickname": "value" "fullName": "value" "office": "value" "unity": "value" and a ...
0
votes
0answers
4 views

Reconfigure PostgreSQL Installation

On Windows (10), is there any way to reconfigure a PostgreSQL installation, specifically when the --with-openssl option was not used? I'm hoping to avoid having to backup, reinstall, and restore ...
0
votes
0answers
44 views

Select JSON elements from nested documnet

I have a JSON document such as: { "type" : "episode", "AssetId" : "urn:episode:ABCDEFG", "_id" : { "$oid" : "12345678" } } Here is the table definition: CREATE TABLE "v22"."assets_exp" ( "_id" ...
0
votes
0answers
29 views

PostgreSQL GIN pg_trgm default operator class - Does Not accept data Type uuid

I was trying to create GIN Indexes for an Array that stores uuid data type. CREATE INDEX IX_Gin_Column1 ON table_name USING gin (column1 gin_trgm_ops); PostgreSQL raised an error: ERROR: ...
0
votes
0answers
101 views

psycopg2.pool.PoolError: trying to put unkeyed connection

I'm trying to get connection pooling to work in my Python Flask web application. The code I am using is as follows: from contextlib import contextmanager import logging from psycopg2 import pool, ...
0
votes
0answers
22 views

index by UDF not used when filtering by subquery column

I have created an index with an expression that calls a user defined function. I expected that when a subquery selects this expression as a column I could filter by this column and have my index used. ...
0
votes
0answers
49 views

ERROR: cannot update table “pg_enum” because it does not have a replica identity and publishes updates

I was trying to update the enum label using the following SQL query UPDATE pg_enum SET enumlabel = 'Cash Recivied' WHERE enumtypid = '1203702' AND enumlabel = 'Cash'; but it throws an error ERROR: ...
0
votes
1answer
35 views

Optimizing one-way syncing of large (wide) tree

I deal with several trees that can vary in size (they all have depths of ~8-10) but it's not unusual for them to consist of ~50-100k nodes. At the moment, the trees are stored using simple parent_id ...
0
votes
0answers
36 views

Postgres replication delay with inserts into large table

I have a large table (over 35 million rows, most rows contain TOAST'd columns) that I've isolated as the source of growing replication delays in my Postgres instance. When I'm doing inserts into this ...
0
votes
0answers
27 views

cannot have more than 2^32-1 subtransactions in a transaction

I'm trying to run this but after 10 hours I get the error "cannot have more than 2^32-1 subtransactions in a transaction". What is your advice for getting around this issue? I'm hesitant to split up ...
0
votes
0answers
9 views

PostgreSQL streaming replication - secondary access

I have read that the streaming replication can be set up so the secondary receives the read only queries, but it is not clear to me how that is "turned on"? With Informix I used a connection manager ...
0
votes
0answers
27 views

Adding LIMIT slows down query

SQL CREATE TABLE actions ( action_id bigint GENERATED ALWAYS AS IDENTITY, block_time timestamptz NOT NULL DEFAULT now(), block_num int NOT NULL, trx_id text NOT NULL, ...
0
votes
0answers
12 views

Postgresql 11 systemd file changed reload to kill; why?

I'm upgrading from Postgres 9.3 to 11, and I do not understand why the ExecReload default command went from ExecReload=/usr/pgsql-9.3/bin/pg_ctl reload -D ${PGDATA} -s to ExecReload=/bin/kill -...
0
votes
0answers
46 views

Sync Production Postgres Database to Laptop - One Way

I am running PostgreSQL 11 on my Windows Laptop and would like to sync with our production database on a (semi) regular basis and then test a number of queries and view creation without running the ...
0
votes
0answers
99 views

postgresql 11 & windows server 2008

I plan to upgrade a PostgreSQL/PostGIS server from 9.6 to 11. The server is running on windows server 2008 R2 64. According to the doc, PostgreSQL 11 and Windows Server 2008 has not be tested. So ...