Questions tagged [postgresql]
All versions of PostgreSQL. Add an additional version-specific tag like [postgresql-13] if that context is relevant.
16,811
questions
7
votes
3
answers
16k
views
Postgres select on a very large table
We've a very large table with more than 2.2 billion rows at present on Postgres 12.5. The total size of the table (including index) stands at 500 GB. There is one query that we need to do in order to ...
0
votes
1
answer
165
views
Is the WAL data text format documented anywhere?
Looking around the Web, I can find plenty of information explaining how to set up WAL replication, and how to get the changes with pg_logical_slot_get_changes or pg_logical_slot_peek_changes. This ...
0
votes
1
answer
574
views
pg_replication_slots: how to determine how far behind a consumer is?
Given the replication api exposed in postgres 9.6.6, for a given replication slot and a consumer, what system catalogs or view could inform how far behind the consumer was the latest state of the wal?
9
votes
2
answers
2k
views
Is there a performance or storage space advantage to lowering time/timestamp precision in PostgreSQL?
PostgreSQL allows time/timestamp to specify a precision:
time, timestamp, and interval accept an optional precision value p which specifies the number of fractional digits retained in the seconds ...
3
votes
2
answers
404
views
Generate series 0.01 to 0.99
Is there any simple method to get a series:
0.01
0.02
0.03
...
...
...
0.97
0.98
0.99
like series generation from 1 to 99
SELECT * FROM generate_series(1, 99)
0
votes
2
answers
124
views
Query slow when a sub select is used
I've created a query which brings back all revenue for specific groups with different columns for YTD, Last year, the year before and the year before that.
It works but extremely slowly and from what ...
1
vote
1
answer
1k
views
Insert values to table only if first Insert was successful
Is there a way I could check the status of an insert query whether it was successful or not. I want to write to another table only if the first insertion was successful.
Below is the table I want to ...
3
votes
1
answer
1k
views
How to reduce the restore time?
I'm using Postgres 9.2. I would like to know how to reduce time to restore process for a big dump (400GB)
We have a database that takes weeks to restore due to the single-threaded nature of index ...
2
votes
1
answer
2k
views
How do I prevent split brain scenarios with the Bitnami PostgreSQL HA chart?
We've been trying to use the Bitnami PostgreSQL HA chart (GitHub repo) in our Kubernetes environment to meet our high availability needs, but keep running into issues where we'd eventually get into a ...
10
votes
2
answers
10k
views
Couldn't open extension control file plpython3u.control : No such file or directory
I want to have python3 in my postgresql database for writing stored procedures.
Being in the psql client, when I enter the command create extension plpython3u I get the error:
couldn't open ...
0
votes
1
answer
143
views
Postgresql log uploading service on Ubuntu Linux
I am trying to create a systemctl service to collect + upload logs for Postgresql 12 on Ubuntu Linux. However, I am expert at none of the two.
While I have set up the server modifying /etc/postgresql/...
-1
votes
1
answer
99
views
db query error: pq: column "systemevents.receivedat" must appear in the GROUP BY clause or be used in an aggregate function
When running the following query I receive an error:
SELECT
$__timeGroupAlias(receivedat,$__interval),
SUBSTRING(message, '(?:[0-9]{1,3}\.){3}[0-9]{1,3}') AS the_address,
COUNT (message) AS ip
...
0
votes
2
answers
6k
views
PostgreSQL: Copy Table Functions to another database schema
as the title said. Can we move or copy existing table functions to another database schema?
I can get all the functions from a database schema using command below (Taken from here):
SELECT ...
0
votes
0
answers
238
views
How to structure SQL database for multiple types of users and roles
I'm building a db for a site where brands can create a storefront and also create staff accounts to manage said storefront.
They'll also be customers who aren't associated with a brand whatsoever that ...
2
votes
2
answers
3k
views
Postgres function not returning any rows but no errors
I have this below function which returns no rows, but no errors as well. Am I missing any statement over here to return the rows?. If I execute the select statement alone I do get results.
CREATE OR ...
-1
votes
4
answers
93
views
Is there some way to automatically "follow"/pre-determine the release date for stable/GA PostgreSQL 14?
I currently have PostgreSQL 12.x installed. I'm putting off my Windows re-installation for the PostgreSQL 14 release, because I want to skip the entire generation 13 for superstitious reasons. Yes, ...
0
votes
0
answers
248
views
PostgreSQL- replication server lags behind the master - memory usage too high
we have a classic physical replication with primary and secondary nodes. The two nodes are also monitored by software.
There are always lags between the primary server and secondary server when there ...
2
votes
1
answer
5k
views
what is best way to handle history tables in postgresql?
I am working on the following database structure/diagram for an app using postgresql 13 server
Is there a better way to design these table relationships with the tables subscriptions_history and ...
1
vote
2
answers
1k
views
PostgreSQL connection URI works, but with parameters fails
I found a weird behaviour connection from command line.
My server is a docker container with postgres user and example as password.
if I connect with URI
psql postgres://postgres:example@localhost:...
1
vote
1
answer
180
views
Would you recommend to use PL/R in 2021 or is it dead? [closed]
And for many years there exists a PostgreSQL extension to use GNU-R directly in the database: https://www.joeconway.com/plr/
Ubuntu packages are also provided and updated for latest PG: https://...
3
votes
2
answers
3k
views
Does PostgreSQL support multi-threaded replication?
Is PostgreSQL replication single-threaded ? Are there any tools to achieve multi-threaded replication ?
I'm asking this since Mariadb support this and currently I'm learning both these dbs.
1
vote
1
answer
270
views
Restore Postgres backup from pgdump.dir
I have been seeing *.pgdump directory mostly to get the backup and restore. But for my new project the backups are stored under dbname<date>.pgdump.dir
Under this directory, the backups are ...
5
votes
1
answer
189
views
Match .csv values as INTs. If one value from a group in the .csv matches one in another group, then merge strings
Here we have two sets of numbers. The problem is that I can't figure out how to get from the input to the output of numbers (DDL and DML below and also in the fiddle here).
current_data
1,2,3
1,4
1,5,...
1
vote
1
answer
204
views
How to use ASSERT to confirm the value of `current_user` [duplicate]
While building up my schema (i.e., dev mode), I would like to use ASSERT to "confirm" that I'm doing what I think I'm doing.
I tried to use
create function api.login(...)
returns void
...
2
votes
1
answer
1k
views
Inserting date range arrays daterange[]
I'm trying to insert the value of an attribute of a tuple, which would be an array of date ranges, i.e. a daterange[], in a previous tuple accepted the value:
{"[2013-05-08,2018-03-22)"}
...
0
votes
1
answer
622
views
pg_settings.statement_timeout changing constantly between 0 and 30000?
When I run the following query constantly every few seconds over a 30 second time period, I sometimes get a value of 0 and other times a value of 30000.
The value changes constantly and I don't know ...
2
votes
0
answers
222
views
How to optimize for performance when querying millions of rows in real-time analytics?
Suppose I have a simple table called events like this:
timestamp
event_id
action_count
apr 5 01:05:00
foo
10
apr 5 01:04:15
bar
20
apr 5 01:06:10
baz
200
apr 5 01:04:30
baz
5
apr 5 01:03:00
baz
...
3
votes
1
answer
114
views
case sensivity incongruity
I'm trying to learn and understand collations and how postgresql compare and order strings but I'm finding an incongruity and I don't know what I'm missing.
The query:
SELECT datname, datcollate FROM ...
1
vote
1
answer
857
views
Help understanding behavior of try_cast() with numeric strings
Erwin made a great, generic try_cast function for postgres (copied below). I'm interested in using it for an EAV schema where attributes are stored with a type field. I can use a similar function (...
4
votes
1
answer
2k
views
Monitoring a specific database with pg_isready
How do I monitor a specific db with pg_isready?
Instance : DBSPG10T1 Version : 10.14 Port : 6241
postgres@postgres # SELECT datname FROM pg_database;
datname
-----------
postgres
template1
...
0
votes
0
answers
315
views
pg_dump fills up memory but does not write to disk
What could cause pg_dump to fill up memory and not write to disk? Strace just hangs at a certain point with no errors and database log does not show any errors. Once memory is filled up and swap is ...
0
votes
3
answers
262
views
Slow query with where and index on some fields
I'm working on a project with the PostgreSQL 12 database.
I have a table of about 500 MB.
The structure of the table is as follows:
create table table_name
(
id serial not null
constraint ...
0
votes
0
answers
382
views
RDS PostgreSQL 12.5 bind slow
I have a PostgreSQL 12.5 db.r5.large instance on AWS RDS (16GB of RAM, 2 vCPU)
While I'm troubleshooting some slow queries, I have seen these in log quite often:
2021-07-28 12:31:14 UTC:172.21.12.81(...
-1
votes
1
answer
1k
views
PostgreSQL Query - how to subtract timestamps and format durations (with builitin functions or otherweise)
I am looking for a solution to a problem with a select statement where I am trying to display the sleep duration.
Sleep Table
Here is the select statement I have so far:
select wakeuptime,hour from
...
0
votes
1
answer
2k
views
Is possible to insert an array inside another array in postgresql?
Let's say I have a table in postgresql called 'Test' which contains a single column called 'Column1' of the type int[]. So, it can contain an array of integers for each row, for example, {1,2,3}. I ...
0
votes
2
answers
79k
views
psql: FATAL: password authentication failed for user "postgres"
I am running postgresql-9.6 on Rhel7. I know that the default/root user doesnt have a password, however i will like to use phpPgAdmin to do my work. the problem is I have edited my pg_hba.conf file to ...
0
votes
2
answers
140
views
Query Times Worsen on 1 Server Vacuum/Analyze on Postgres 9.6
We have a query which is called approx 15,000 times during the day. The query itself should take a few milliseconds to run.
I like to think we have the appropriate indexes in place for the query:
...
1
vote
0
answers
161
views
How to design and query graph relationships in PostgreSQL?
I am having difficulties trying to design a graph-like relationship in Postgres. For example:
create table action (
user integer references user(id),
relation varchar(255),
target integer ...
0
votes
0
answers
225
views
Multiple tokens per account
In the context of user account management, I'm using Firebase. So for each login, the front-end sends an Auth ID, a FCM token (for sending notifications to the user), and the usual user details. Each ...
19
votes
2
answers
13k
views
PostgreSQL Index Caching
I'm having difficulty finding 'lay' explanations of how indexes are cached in PostgreSQL, so I'd like a reality check on any or all of these assumptions:
PostgreSQL indexes, like rows, live on disk ...
0
votes
0
answers
1k
views
How can I exclude Materialised Views from a pg_dump
I'm doing a pg_dump of an AWS RDS database so I can use it for local development, but there are some large tables I want to exclude. I've successfully removed them using the --exclude-table=blah* flag ...
2
votes
2
answers
3k
views
Possible for two simple updates on the same table to deadlock?
Table A is:
id integer
version varchar
data jsonb (large data 1mb)
fkToBid integer (references B.id constraint)
Table B is:
id integer
other...
Processes are aggressively running the two updates ...
1
vote
1
answer
427
views
Use Case for Table Partitioning?
I am looking for advice on large tables and partitioning
The Data
I have two resources: participants and measurements
There are 500,000 participants
Each user will have 8,760 measurements
That means ...
5
votes
2
answers
2k
views
Using postgres_fdw to connect from one read replica to another
I have two postgres database servers in AWS, A and B. These databases have different tables. Both databases also have their own read replica.
My goal is a simple solution for doing cross database ...
2
votes
0
answers
553
views
Postgres ltree: Get all accessable resources by groups and sub groups
Have following Data scheme:
Roles:
Each role has either a resource assigned or a resource_group. A user wich has a role on a group has also access to all resources that are within the group or within ...
10
votes
5
answers
16k
views
How do I set the external editor for PostgreSQL's psql client?
What do I put in ~/.psqlrc file to set the default editor?
The editor I want to use (Textmate) is at /Users/username/bin/mate.
I found the place in the docs where they talk about it, and tried ...
2
votes
2
answers
647
views
How do you handle "undo" in databases in general, and PostgreSQL specifically?
I have a complex PostgreSQL-based system which I made myself from the ground up. It contains tons of invaluable (to me) data which I don't want to lose or get corrupted.
I'm always paranoid about ...
3
votes
0
answers
240
views
Nested cursor under update command [closed]
I am building a procedure to label database rows if certain records meet some conditions.
I am looping over rows using cursor and for every row I check for some conditions, if conditions are met I ...
4
votes
2
answers
8k
views
How can I change the language of Postgresql logs
Right now my db get disconnected when calling my user function. I would like to ask for help about it but the message are in Spanish.
How I make my logs be in English? I can understand what said, ...
1
vote
2
answers
3k
views
High autovacuum CPU/IO usage from database with more than 10k append-only tables
In my system I have more than 10k tables that are very similar, I only do insert operations in these tables and delete old data via a cron schedule one time per day.
These insertions come each minute, ...