Questions tagged [postgresql]
All versions of PostgreSQL. Add an additional version-specific tag like [postgresql-13] if that context is relevant.
16,378
questions
0
votes
1
answer
433
views
support of multiple databases on different hosts by pgpool2
Let's assume We have 2 databases:
Database1 is located on host1 and host2
Database2 is located on host3 and host4
If I'll use pgpool2 to connect to these 2 databases with single pgpool config for ...
CommunityBot
- 1
0
votes
1
answer
571
views
Repmgr : It automatically promotes to new master but other standby stopped
I'm trying to setup postgre with repmgr and here is the rsult
The scenario is,
i tried to spinup 1 primary and 2 standby.
then i stop the primary so the postgres-2 got promoted.
Unfortunately the ...
CommunityBot
- 1
1
vote
1
answer
361
views
Loop Block with execution doesn't persist
I need to iterate over a result set and update records from the data. I am using an anonymous block but the information does not persist.
Like this...
do $$
declare dados Record;
begin
for ...
CommunityBot
- 1
5
votes
1
answer
668
views
Adding a Primary Key to a Large PostgreSQL Table with High Traffic
I need to add a primary key to a large PostgreSQL table (approximately 2TB) with high traffic. This is a critical operation, and I'm looking for guidance on how to do it efficiently.
I have already ...
- 170k
3
votes
1
answer
387
views
Installing Postgresql parray_gin extension on mac, include not found
I am trying to install parray_gin in PostgreSQL 10 on macOS, but I can't get it to build.
I'm running pgxn install parray_gin. This first gave me a missing stdio.h error, so I installed the xcode ...
CommunityBot
- 1
1
vote
0
answers
17
views
Migrating from PostgreSQL to MySQL (data with \r\n)
I'm trying to migrate a database from PostgreSQL to MySQL on an Ubuntu 23.04 system (latest version of both).
So far, it looks like things are fine, except for one problem. In one of the character ...
- 113
1
vote
1
answer
1k
views
PostgreSQL - Replica Recovery Process Stuck frequently (epoll_wait taking too much time)
We have Master Slave Setup with Asynchronous Streaming Replication (Replication Slots). Frequently our Replica's entire Postgres processes seems to be doing nothing (or stuck?). Since ...
CommunityBot
- 1
0
votes
1
answer
20
views
I am looking for a Postgre SQL query which should list out all tables being used in a function
PostgreSQL query which should list out all tables being used in a function.
Need help on this.
exmple: Let say Function_name is 'A' and 4 tables are being used in that function(X,V,B,N). Need query ...
4
votes
2
answers
5k
views
xlog flush request is not satisfied in log files
Today I saw that Postgres produces error messages related to possible data corruption (?). Everything's working as expected so I didn't realize anything was wrong.
ERROR: xlog flush request 6E/...
CommunityBot
- 1
0
votes
1
answer
13
views
Postgres table structure optimization: separate small field from large jsonb fields
A table has the following structure:
a: id
b: large jsonb
c: large jsonb
d: timestamp
Looking at stats in 2 weeks, see many updates:
90k requests updates the "d" field alone (total time: ...
- 45.7k
0
votes
1
answer
1k
views
Conditional default value based on sequence
There is a PG table example with two fk columns table_a_id, table_b_id and position::int column. It's guaranteed there will be only one column filled table_a_id or table_b_id.
CREATE TABLE table_a(
...
CommunityBot
- 1
2
votes
1
answer
2k
views
How to design tables for airbnb style booking system [closed]
all. My English is not very good, but I will try to describe my question as clear as possible.
I want to design a airbnb(https://www.airbnb.com) style booking system, this kind of booking system ...
- 103
3
votes
1
answer
1k
views
speed up PostgreSQL calculations via OLAP warehouse?
So I have asked these three questions:
Percentile rank that takes sorted argument (or same functionality) in PostgreSQL 9.3.5
https://quant.stackexchange.com/questions/29572/building-financial-data-...
CommunityBot
- 1
0
votes
0
answers
19
views
Performance Issues with Concurrent Queries During Mass Update in PostgreSQL
I have a PostgreSQL table with a large amount of data (2 terabytes) where I recently performed a mass update to add UUID values to a column (uuid_column). While the update was successful, I am ...
- 213
0
votes
1
answer
47
views
PostgreSQL 15 pg_read_file absolute path not allowed
When not superuser but granted the pg_read_server_files role,
with statement
select * from pg_read_file('E:\PG_VERSION');
PostgreSQL 15 got a error "absolute path not allowed" while 14 is ...
- 37.6k
0
votes
0
answers
23
views
Why doesn't PostgreSQL lookup by PK first (when `WHERE` contains more conditions)?
When I run this against my PostgreSQL DB (where the id column on the courses table is the primary key, and there's also an index on is_deleted, which is a boolean):
SET enable_seqscan = OFF;
EXPLAIN ...
- 121
0
votes
1
answer
679
views
'Could not fsync file' while running VACUUM FULL on a table
I'm getting an error for around half of my tables in Postgres while trying to run VACUUM FULL.
For example, I have a table named 'qwerty', and when i try to vacuum it i get the following error:
ERROR:...
CommunityBot
- 1
1
vote
1
answer
40
views
Postgres heavy updates cause fast table growth
I have a question related to an incident that happened on one of our Postgres DBs.
We have a backend service that keeps track of user's activity in some kind of online event. The service uses Postgres ...
- 37.6k
1
vote
1
answer
385
views
Massive slowdown after doing an ALTER to change index from int to bigint, with Postgres
I have a table like this:
create table trades (
instrument varchar(20) not null,
ts timestamp not null,
price double precision not null,
quantity double ...
CommunityBot
- 1
3
votes
2
answers
445
views
Getting Postgres CREATE TABLE statements
I created some tables (9, to be exact) with a Django (1.9.6) migration and now I'm trying to get simple CREATE TABLE statements for them. I tried this answer, but using pg_dump in this way gives me ...
CommunityBot
- 1
0
votes
1
answer
18
views
Postgres changing data type from timestamp to timestamptz (with time zone)
I recognize there are some similar questions, and I've read quite a few, so pardon me if I just missed the answer.
I have data that was stored as a TIMESTAMP data type, but now we want it to be stored ...
- 11
0
votes
1
answer
1k
views
COPY command only works as command line argument, not as part of script
No rows are being copied when I try to copy a CSV from stdin. To be more specific, this works when writing the command on the cli, but not when read as part of a file. Why is reading SQL commands from ...
CommunityBot
- 1
0
votes
0
answers
5
views
Java Inheritance - Hibernate6 + JPA - How to prevent selecting all child tables when joining on parent?
I'm working on a Java application that uses Hibernate6 + JPA.
I have classes like
class Thing {
Parent parent
}
class Parent {
}
class ChildA extends Parent {
}
class ChildB ...
- 9
0
votes
0
answers
15
views
Migrate existing Postgres table with foreign key references to declarative partitioning
I want to apply declarative partitioning to a Postgres table referenced by many other tables with foreign key constraints.
As far as I can tell, Postgres provides limited support for this. The entire ...
- 179
0
votes
1
answer
18
views
`pg_restore` unable to read `toc.dat` created by `pg_dump -Fd`: `pg_restore: error: expected format (1) differs from format found in file (3)`
The pg_dump -Fd mydb -f mydb.dump command creates a mydb.dump directory with a toc.dat file. The pg_dump man page mentions that pg_restore is able to read this file, but after skimming its man page ...
- 111
0
votes
1
answer
490
views
postgresql function to automatically merge and add two jsonb values from different tables when one is updated and store it in a third
Lets say that tableA has column columnA with jsonb data in it like the following:
{
"A": 5,
"B": 15,
"C": 20
}
and tableB has columnA with jsonb data in it like ...
CommunityBot
- 1
0
votes
1
answer
63
views
Why did the number of live rows fetched by index scans drop after table ANALYZE?
We use PostgreSQL 12 and have a simple table, event_participant, storing 100 GBs of data.
event_participant has all the necessary indexes, so all rows are fetched using them, i.e., no rows are fetched ...
- 29
0
votes
1
answer
15
views
Adding subtotals using GROUPING SETS
For a data table I would like to create the following table:
store
department
gender
workers_count
A
Light
Male
10
A
Light
Female
5
A
Plumbing
Male
20
A
Plumbing
Female
0
2
35
B
Food
Male
12
...
- 45.7k
0
votes
1
answer
28
views
Slow join on geometry fields
My situation is somewhat similar to this earlier question: In PostgreSQL I have one table containing point geometries (P), and another containing polygons (G). I would like to join on the geometry ...
- 45.7k
0
votes
1
answer
35
views
Better way to return list of FK "parent" tables?
Here's what I currently do in Postgres 9.6. (I know it's EOL; that's not my choice to make.)
I don't want the definitions, FK names, or any of the other clutter you get with \d <table>. Just ...
- 170k
0
votes
1
answer
390
views
How to return rows from multiple tables with a Postrgresql function
This is the selection I want to make:
select figurita.codigo,figurita.imagen, jugadores.nombre, jugadores.apellidos, jugadores.numero,jugadores.peso,jugadores.altura,jugadores.fechanac,
posiciones....
CommunityBot
- 1
2
votes
1
answer
16k
views
Is there any way to clear all tables using Liquibase?
I'm currently using Liquibase for creating test data in my database before each test run.
I came across a problem where some of the test cases need special data, while others need the tables to be ...
- 24.8k
0
votes
0
answers
16
views
Column ___ of relation ___ does not exist in csv migration -- is Liquibase ignoring the schema I configured?
edit: I fought this too long and went with SQL insert statements instead, since I was generating the CSV from a JS script it was easy to translate over. I think there's a bug in this loadData command. ...
- 207
0
votes
1
answer
463
views
Ram consumed by single query in Postgres using SqlAlchemy
I have a list of 36 queries ranging from low complexity(No joins) to high complexity(joins with subquery). I want to find the RAM Consumed by each query.
This is required for performance testing for ...
CommunityBot
- 1
14
votes
2
answers
7k
views
What does it mean when a session is highlighted in red in the PgAdmin dashboard?
I've noticed sessions being highlighted in red recently though this never occurred in the past which leads me to believe it is a new feature. It appears to only occur on hanging queries. I looked ...
- 24.8k
2
votes
2
answers
3k
views
Is it possible to never run vacuum full in PostgreSQL
Since a VACUUM FULL locks tables, it is unacceptable for us in our production environment.
Is it possible to only run VACUUM and never VACUUM FULL on a production system?
Does VACUUM make space ...
- 29.3k
1526
votes
9
answers
3.1m
views
How do I list all databases and tables using psql?
When I log in with psql --username=postgres, how do I list all databases and tables?
I have tried \d, d and dS+ but nothing is listed. I have created two databases and a few tables with pgAdmin III, ...
0
votes
1
answer
699
views
Calculate the duration in an interval and outside of events that can last over several days
I need to calculate the total duration of events in a given time interval and on a given day of the week with PostgreSQL 11
For an event on the same day no problem. But over several days I manage to ...
CommunityBot
- 1
0
votes
1
answer
20
views
postgres db 'function mul(double precision) does not exist'
I am currently facing a problem on an postgres database. The db server is a data warehouse for a software we are using. For every tenant in the software system there is a database on the postgres ...
- 45.7k
0
votes
1
answer
438
views
Azure Data Factory - copy data activity into a Postgresql database (on an IaaS Windows Azure vm, not Azure Database for Postgresql (PaaS))
I have a Postgresql database on a Windows vm (not a PaaS Azure Database), and I need to import csv files into it daily. ADF seems like a perfect fit, but I see that a standard Postgresql db is not a ...
CommunityBot
- 1
0
votes
1
answer
18
views
how to call nextval for a sequence that have a mixed case name
Using pgAdmin4 I just created a table confs with a column named conf_ID of type SERIAL. This has automatically created a sequence named confs_conf_ID.
Now, when I try to get a new value I always get ...
- 4,882
1
vote
1
answer
627
views
Django Multi Table Inheritance and Preserving Child and Child History
Place can have many different one to one fields, that’s why it can have bookstore, restaurant, and hardware store. But can the same place be both a bookstore and a restaurant at the same time? Now if ...
CommunityBot
- 1
1
vote
1
answer
7k
views
Why are my postgres logical replication workers crashing after syncing a certain amount of data?
I am trying to setup a logical replication between two postgresql 12 DBs run in Docker containers on AWS ECS. I gave the task 2 vCPU and 6 GB of ram so quite luxurious for a DB with ~1e7 rows in two ...
0
votes
2
answers
603
views
PostgreSQL - "Fatal: could not access file"
I accidentally ran
ALTER DATABASE mydb SET session_preload_libraries = ''; -- empty string
and now I can't log in to my PostgreSQL (v.14) database. I'm getting
[58P01] FATAL: could not access file &...
- 1
0
votes
0
answers
11
views
How to restore PostgreSQL WAL from one Primary database to another Primary database nodes?
Suppose I have two separate primary database PrimaryDB1 and another is PrimaryDB2. I want all the transaction of PrimaryDB1 replicate into PrimaryDB2 by WAL replay. How can I do it?
I don't want other ...
- 45.7k
2
votes
2
answers
3k
views
Postgresql return results of dynamic SQL
I'm using Postgresql, version 12.
My goal is to create a function, or stored procedure, which generates a dynamic select which is returned by the function or stored procedure. The difficulty is that ...
CommunityBot
- 1
0
votes
1
answer
2k
views
role "postgres" already exists when restoring cluster
I backed up a server with
pg_dumpall.exe --file "backup.sql" --host "db" --port "5432" --username "user" --no-password --database "postgres" --verbose ...
CommunityBot
- 1
0
votes
1
answer
66
views
How do I query out the largest value using an index
I have a table with several terabytes of event data in a very simple (id, bucket_id, data, created_at) schema and there is an index like so
create index index_events_on_created_at_and_bucket_id
on ...
- 170k
0
votes
1
answer
2k
views
Cleaning up bad data with REGEXP_REPLACE too aggressive with non latin chars
Trying to use REGEXP_REPLACE to clean up a text field in a table.
Here's my query:
select title original ,REGEXP_REPLACE(title, '[^a-zA-Z0-9]+', ' ') clean
from tmp_table2
WHERE txn_title like '%?%'
...
CommunityBot
- 1
1
vote
0
answers
12
views
PostgreSQL - Table partitioned on date doesn't prune partitions when querying with a daterange
Our tables are partitioned by month on the dt date column
CREATE TABLE IF NOT EXISTS ta
(
link_dir text COLLATE pg_catalog."default" NOT NULL,
dt date NOT NULL,
tod time without ...
- 519