Questions tagged [postgresql]
All versions of PostgreSQL. Add an additional version-specific tag like [postgresql-13] if that context is relevant.
14,090
questions
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(),
...
