Questions tagged [postgresql]

All versions of PostgreSQL. Add an additional version-specific tag like [postgresql-13] if that context is relevant.

Filter by
Sorted by
Tagged with
0
votes
0answers
20 views

ERROR: Error when fetching backup: pg_basebackup exited with code=1 when start patroni service for a replica

I created a cluster including 3 servers via Patroni. Everything was alright like failover, switchover... but when I try to add another replica server I am getting an error like this: Eki 25 13:19:22 ...
0
votes
1answer
22 views

PostgreSQL 13.4: Setting synchronous_commit using ALTER SYSTEM doesn't change it

We're currently building a PostgreSQL cluster using PostgreSQL 13.4, Patroni, Consul and Confd. The setup was successful (Patroni cluster is replicating with one master and one replica), and we are ...
0
votes
1answer
21 views

How to install PostgreSQL with special version by Ansible role?

I use the ansible role installed PostgreSQL. Its default version is 10.6. How to set another version to install? Such as 12.4. $ psql --version psql (PostgreSQL) 10.6
0
votes
1answer
36 views

PostgreSQL trigger to update date modified [closed]

I am attempting to update the column modified whenever any of the fields are updated in the table users with a trigger in PostgreSQL version 13. CREATE FUNCTION myschema.on_users_update() RETURNS ...
0
votes
1answer
21 views

Creating manual superuser in cloudsql for postgres

I’m using google managed cloudsql database and I’m trying to create my own superuser manually and I’m getting below message Note: I’m logged in via existing superuser only(mycurrentuser_dev). ...
0
votes
2answers
48 views

Update with subquery referencing the main table

I'm trying to understand how to filter my subquery in context to the main query. Ultimately I'm trying to get the MAX value from the latest record BEFORE the date of the current record. Here is where ...
0
votes
1answer
26 views

How properly create index for a referenced table

I have a table with thousands of records with vehicles data, and this table is referenced by the table, where images are stored for these vehicles. This second table about millions of records because ...
0
votes
0answers
15 views

pgadmin column sorting

I recently installed pgadmin 4.6.1 and I noticed that columns are sorted alphabetcally by name. Sometimes this can be annoying. Does anybody know if it possible to change this behaviour?
0
votes
0answers
26 views

postgresql database didn't restored in ubuntu and got blank

i try to restore databse form my pc(windows) that i send it from pscp i use this script : sudo -u postgres pg_restore -U postgres -f /home/abura/first_backup.sql and got blank, so i randomly click to ...
0
votes
0answers
46 views

pg_dump with proper table dependency analysis?

Is it possible to dump then recreate with proper table analysis, so for example if table A depends on table B, then table B will be dumped first, then the table A something like CREATE TABLE B(...); ...
0
votes
1answer
38 views

Update values on table reverse order (Postgresql)

I have a table with three columns (way_id, node_id, sequence_id) with a unique constraint on way_id and sequence_id. If I want to insert an item at row 10, I want to increase all those with a ...
0
votes
1answer
62 views

Upgrading Postgres major version using logical replication

We have considered some options to upgrade our Postgres servers from the current (10) version to the latest (14). We decided no to do on the primary - since we need to do with the link option (so in ...
1
vote
0answers
51 views

PostgreSQL: Taking into account correlation between null / not null values of columns during planing

Question: Is PostgreSQL able to take into account correlation between null value of one column and not null values of other columns during planing? Does "Cross Columns Stats" feature allow ...
0
votes
0answers
10 views

Barman Recover Unable to start postgresql 9.6 FATAL: could not access status of transaction 722811824

I have done a barman recover to a different server, however I am having issues starting postgresql 9.6 FATAL: could not access status of transaction 722811824 DETAIL: Could not open file "...
-2
votes
2answers
50 views

joining table 1 with table 2 where there are multiple matches in table2 and you want 1 but definitely not the other

Here's a simplification of the problem... suppose you have a bunch of socks in a drawer. Each sock has an id associated with it, but they might be different striped colors. Let's say the stripes are ...
1
vote
0answers
34 views

PostgreSQL tds_fdw Column Truncated to 2048 Characters

I set up several tds_fdw foreign tables on a Postgres 13 server accessing a MSSQL 2014 server. Everything has worked just fine, until I had a need to select from a table with a large column (nvarchar(...
0
votes
1answer
19 views

Problem setting up postgres [closed]

So I am trying to connect to freshly installed postgres. Whenever I try to create a database or use any commands nothing happens. for example postgres=# changes to postgres-# after running a command, ...
0
votes
1answer
27 views

postgresql blocking behavior for table dependent insert

This question follows up the previous simpler question: postgresql blocking behavior Old context: I would like to understand the blocking behavior of postgresql. Consider I have an insert statement ...
1
vote
2answers
35 views

Preventing Transaction ID Wraparound with vacuum freeze

I recently got few DBs to maintain. I just come up about this "Preventing Transaction ID Wraparound Failures". I checked my DBs and got the numbers: I did vacuumdb -a -v on this DB, and ...
1
vote
1answer
26 views

Postgres creating 'histogram_bounds' statistics on JSONB column

I have two database tables (A and B), each containing roughly 1.6m rows, and each having the following structure: CREATE TABLE tablename ( identifier text NOT NULL, geometry geometry(GeometryZ,...
-1
votes
1answer
18 views

postgresql blocking behavior

I would like to understand the blocking behavior of postgresql. Consider I have an insert statement starting at 1:00 (stage A) completed at 2:00 (stage B). However, I have a few complex select ...
0
votes
1answer
50 views

why does this correlated subquery return an error in PostgreSQL?

Here is the query in question : SELECT name, totals1.all_purchases FROM accounts, (SELECT orders.account_id, SUM(total) AS all_purchases FROM orders GROUP BY orders.account_id) totals1 ...
1
vote
0answers
13 views

Grafana dashboard snippet for prometheus postgres_exporter

I'm managing a fairly large postgres cluster (15 instances), and aside of the usual dashboard containing a few stats, I was looking for a curated dashboard that could graph the highest number of ...
1
vote
1answer
22 views

What happens to written WAL logs if a transaction is aborted in the middle? [duplicate]

I was thinking about scenario below. BEGIN TRANSACTION; INSERT SOMETHING; INSERT SOMETHING; ##CLIENT COMPUTER EXPLODES If i understand correctly, after each INSERT statement, a log entry is written ...
0
votes
2answers
34 views

parallelize select/insert for postgresql

I have a relatively big postgresql table (timescaleDB), 200GB ~ 1B rows, index by timestamp and id. I realize a lot of times, my very task is only handled by 1 process. I believe my query can be split ...
0
votes
0answers
32 views

How to optimize the following query by adding more indexes?

I am trying to optimize a query which has been destroying my DB. https://explain.depesz.com/s/isM1 If you have any insights into how to make this better please let me know. We are using RDS/Postgres ...
0
votes
0answers
19 views

Replace Postgres table while keeping foreign keys in other tables [duplicate]

I have a project where I'd like to replace all the rows in a table periodically with new data. That data will 99% of the time have the previous rows with updated data and some new ones. There are ...
0
votes
1answer
10 views

postgresql Modifying existing unique index

I have this table on PostgreSQL: example_dev=# \d products Table "public.products" Column | Type | Collation | Nullable | ...
0
votes
1answer
28 views

I have permission to the table, but not to a view that uses the table

I am having a behaviour I cannot understand, and I have no idea where to look for solutions. I have a view called wms_view_orderline that gets information from the wms_orderline table. When I SELECT ...
0
votes
0answers
14 views

PostgreSQL parallel query not working on CTAS

Hi have a query that looks like this: create unlogged table analytics.processor_data as select col_a, col_b from tbl_a a join tbl_b b on a.id = b.id; It's all good, however, it ...
0
votes
1answer
72 views

FATAL: no pg_hba.conf entry for replication connection from host

Ok before this gets flag as duplicate, bear with me. I'm trying to connect two replicas (.12 & .13) to a primary PGSQL13 DB (.11) in a lab environment using synchronous stream replication but it ...
0
votes
0answers
16 views

Please help me figure out the structure of the ecommerce store database

I'm doing a study project (online smartphone store) in python + flask + postgresql. I'm still new to this and would like to ask for help with the db structure. I made a rough diagram. Can you tell me ...
1
vote
1answer
39 views

Postgresql indexes: enums and booleans first?

When writing indexes, should I put the fields that represent enums first? That is, these fields have limited possible values: [0,1,2,3], for instance. So, I might make an index "my_index" ...
0
votes
1answer
57 views

pg_dump fails with "LOG: server process (PID 6055) was terminated by signal 9: Killed"

trying to export database with pg_dump I use parameter "-j 30" to speed up export but issue is that system kills Postgres processes kernel: Out of memory: Kill process 6055 (postmaster) ...
1
vote
0answers
19 views

AWS RDS Postgres - Same query run on same-ish data on two "identical" servers runs 100x slower [closed]

Slightly noob-ish question/problem: I have a query that behaves differently in two "identical" setups - when run against second server, it is 100x slower. Setup is AWS RDS Postgres 12 - two ...
0
votes
1answer
44 views

How to remove 60+ million rows of Data using Temp Table using Postgresql 9.6

Am looking to delete a vast amount of data in our database, we are not sure where to place the deleted data for now but to have a separate copy is what we are looking to do. I explored using a CTE ...
0
votes
1answer
33 views

Update Postgres-12.2 to Postgres-12.8

OS version: RHEL 7.9 I want to update Postgresql minor version. I have Postgres-12.2 installed using source build in /mypath/bin directory. I can't update that Postgres-12.2 using source for some ...
0
votes
0answers
10 views

repmgr standby unregister

PostgreSQL version 10.16 Repmgr version 4.4 After issued repmgr standby unregister on the standby node, the database still receiving replicated data from the primary server. I queried the repmgr.nodes ...
0
votes
1answer
22 views

PostgreSQL IO Cost logic

I've been trying to get an IO cost number, similiar to how MS SQL provides it, but in PostgreSQL. I have read that using EXPLAIN ANALYZE SELECT ..., there will be a cost in the output, but the cost ...
0
votes
0answers
31 views

enforcing serial sort order with user-sortable entities in multi-tenant application (postgresql)

Suppose you have the below tables/relations. board_cards represents a user-orderable entity. The order of each card is relative/constrained to its single board_lists. board_lists represent a ...
0
votes
0answers
79 views

Postgres is extremely slow through localhost, for any command

Postgres 13 in my Windows 10 machine is extremely slow. Even with Postgres shell, every single command needs 22 seconds to be executed. Either it is a single query in a nearly zero-rows DB, or a ...
0
votes
3answers
38 views

Do I have to add accounts to "pg_hba.conf" to allow the account to use the database?

I was following a tutorial to use PostgreSQL with a server application (both are running on the same computer). The tutorial was using SQL commands, but I used pgAdmin. I added a new user 'gitea' with ...
0
votes
0answers
18 views

Is there some way to "make technically sure" that your database is intact after a move/restore?

Regardless of what method was used to move/restore/upgrade/whatever your PostgreSQL database, is there some way to make sure that it's still intact? I don't mean manually looking at the table tree in ...
1
vote
2answers
51 views

AWS Aurora Postgres 12 Setting Collation

I posted this on stack overflow as well, but didn't get much traction over there. If I am not able to get some thoughts on here, then I will probably open a support ticket with AWS. I am trying to set ...
0
votes
0answers
12 views

Partial One to Many DB replication

We operate a number of self-service terminals. We have a central DB that holds all the data in several tables and then there are local DBs in every terminal that have the identical schema to the ...
4
votes
2answers
153 views

Generate all combinations of a list of strings

I'm trying to generate all combinations of a list of Strings list = ['A', 'B', 'C', 'D']. I want to generate all possibilities and then search in the DB ABCD, ABC, ABD, ACD, BCD, AB, AC, AD, BC, BD, ...
0
votes
0answers
52 views

AWS DMS Task failed with error: Error executing source loop; Stream component failed at subtask 0

I want to migrate my PostgresDB hosted in Citus cloud service to AWS RDS Aurora Postgres. I am using AWS DMS service. Have created task but getting following errors: Last failure message Last Error ...
-1
votes
1answer
28 views

sum with combination

I reproduce the table in image and result I expect. So, I like sum for each item withe the sum of related item. But some item are null or join to himself. I think some array can help me but I don't ...
0
votes
2answers
37 views

constraint check values with regular expression

In a table with a field code, I want to authorize the user to choose one value among a list. Here is a sample of the values of the list : A|AG|AL|AS|B|C|D|DA I added a constraint on code like this : ...
0
votes
0answers
23 views

De-duping postgres date ranges with other date ranges for analysis

In my app, there is a table called schedules which have mixed types type: 'project', 'pto', 'sick' and have a date range date. CREATE TABLE public.schedules ( id int8 NOT NULL DEFAULT next_id(), ...

1
3 4
5
6 7
282