Questions tagged [postgresql]
All versions of PostgreSQL. Add an additional version-specific tag like [postgresql-13] if that context is relevant.
13,435
questions
3
votes
2answers
83 views
Optimization of recursive view
I have an optimization problem when using a recursive view on PostgreSQL. When I perform a trivial query using this view, the execution time is abnormally long.
To expose my problem, here is a ...
0
votes
1answer
24 views
Measuring how long PostgreSQL waits for locks
I want to figure out how long PostgreSQL spends waiting for locks during a period of time (during this period of time, many requests are served by PostgreSQL).
What I know
PostgreSQL system table ...
0
votes
1answer
25 views
PostgreSQL + how to clean pg_xlog log without damage the PostgreSQL service
we are using postgresql for ambari DB , in our linux server - rhel 7.2
postgres -V postgres (PostgreSQL) 9.2.13
we noticed that pg_xlog folder consuming 198g , and its gives /var to get 100% size
so ...
1
vote
0answers
45 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 ...
0
votes
0answers
13 views
Postgres jsonb field: remove an element from an array by the element id
Figure a Postgres 12.5 jsonb field containing the following:
[
{id:1, name: 'one'},
{id:2, name: 'two'},
{id:3, name: 'three'}
]
How to write the request that will match: "drop the object ...
0
votes
1answer
30 views
Almost 100% CPU Postgresql
When I restart Postgresql 11, the CPU shows basically no CPU usage. But after a while the CPU is occupied by "kdevtmpfsi". What is this and how can I fix this?
1275 postgres 99.8 % /...
0
votes
1answer
19 views
Check constraint to guarantee ltree ancestors
I ran into an issue where we had a tree structure that looked like this:
select title, tree_path from my_table where tree_path <@ '3';
title | tree_path
-----------------------
item A | 3
...
0
votes
1answer
337 views
Retrieve or reset the password for PostgreSQL on Linux
I have installed Postgres on my Linux system. I forgot the admin password I want to reset the password. How can I do that?
14
votes
5answers
17k views
Is there a means to set the owner of all objects in a PostgreSQL database at the same time?
The Stack Overflow Q & A Modify OWNER on all tables simultaneously in PostgreSQL describes some nifty ways to change table and other objects to a specific user, and it works swimmingly, however ...
3
votes
1answer
3k views
Is it possible to refresh PgAdmin 4 query without restarting a new query after disconnect?
Sometimes I leave a PgAdmin session alone for a while and when I come back to issue a query, I get an error
Server closed the connection unexpectedly.
This probably means the server ...
0
votes
2answers
2k views
How to get sum of last previous X days of everyday? [closed]
Let's say I have sales number of last 60 days. How would like to get sum of every last X days of everyday.
For example my graph will include
Sum of Day - 60 to 30
Sum of Day - 59 to 29
Sum of ...
0
votes
1answer
201 views
Restoring postgres backup with WAL-E fills up disk [closed]
I am running Postgres and WAL-E in a Docker container built from a custom image based on postgres:9.4. WAL-E works great and I have restored from backups a number of times. However, the backups take a ...
0
votes
1answer
20 views
How can I have multiple foreign keys in one table? [duplicate]
I have a setup where I have accounts and teams. One account can belong to multiple teams and one team can belong to multiple accounts.
How should I go about designing this in Postgres?
0
votes
3answers
151 views
How can I improve the performance of this query?
I've tried to add an index on records.status, but the cardinality is so low (<5 unique values) a seq scan was still used.
The query:
select distinct "events".* from "events"
inner join (select "...
-2
votes
0answers
18 views
How to calculate difference between two days in hours in postgreSQL and return as ceil of days? [closed]
I want to calculate the difference between two dates in days such that for hours from 1-23 we consider it as a day. for example: Date1 = '2021-06-15 01:52:00.926+00' Date2 = '2021-06-15 02:52:00.926+...
0
votes
1answer
17 views
Extract JSONB column into a separate table
I have a table with roughly the following columns:
CREATE TABLE records (
id integer NOT NULL,
...
metadata jsonb DEFAULT '{}'::jsonb
)
... includes the rest of the columns which have ...
0
votes
1answer
43 views
POSTGRES: Calculate Portfolio values for each day
I am working on a query to calculate the daily value of a portfolio of stocks.
I have a transactions table:
id | portfolio_id | stock | transaction_type | shares | price | transaction_date
1 | PA1 ...
0
votes
0answers
12 views
Postgres Switchover Failed - ERROR: requested starting point AF/7D000000 on timeline 1 is not in this server's history
My switchover to the standby replication was failed. So I have moved towards bringing it up. DBs are online but I see the log file like this:
2021-06-15 16:59:40.339 CEST > ERROR: requested ...
0
votes
1answer
19 views
How to run postgres in-memory on windows and sync to FS when a temer runs out?
So Progress is slow for me (in terms of query and update times) on SSD and it is ok in my case: I use it as a mix of Graph database + Table processor + NoSQL JSON trees so lots of loops, graphs, trees,...
2
votes
1answer
16 views
Is vacuuming a PostgreSQL materialized view necessary?
I understand it makes sense to analyze a Materialized View (MV), but since MVs are not updateable, does that imply that we need not ever vacuum a MV?
0
votes
2answers
155 views
PostgreSQL jsonb_set performance
I searched the internet and PG documentation for hours and couldn't find any benchmark for it.
In MySQL 8.0 they specifically state that now the optimizer is smart and can do in-place modification of ...
2
votes
1answer
165 views
Bulk load data and provide row-by-row feedback
We want to allow our users to import CSV files containing many thousands of records to "pre-load" a particular table in our system. It's not strictly a one-time operation, it may be ...
0
votes
0answers
12 views
Authenticating Postgresql users with Active Directory
Following this procedure, I've arrived at the step:
(if you do not have a ticket already, run: kinit sfrost@DOMAIN.LOCAL)
But when I do that...
$ hostname
blarg
$ kinit -V ron@EXAMPLE.COM
Using ...
3
votes
1answer
100 views
Suggestion for nearly gap-less sequences in postgres
In PostgreSQL sequences are designed to have gaps for reasons mentioned in this post https://stackoverflow.com/questions/9984196/postgresql-gapless-sequences. My question is: could sequences not be ...
1
vote
1answer
150 views
PostgreSQL ensure no concurrent reads
I'm sorry if it's a very basic question, but would really appreciate educated advice on this:
Say I have an operation, which first has to read from a table and then insert a row if the read value ...
0
votes
1answer
453 views
Barman: What's the best way to switch from backup_method postgres (streaming) to rsync (ssh)?
My production postgresql server is currently backed up with barman using the backup_method postgres (streaming server). I would like to switch to rsync method (ssh) to benefit from better compression. ...
0
votes
1answer
23 views
Export Postgres sequences only
Is there a way using PG_DUMP or any other commands to export only sequences from an existing database?
0
votes
2answers
124 views
Retrieving database dump after copying from remote server
I used command
pg_dump -C -h remotehost -U remoteuser db_name | psql localhost -U localuser
to copy database on remote server to my local machine.
Can I somehow retreive the dump obtained after ...
0
votes
1answer
288 views
PostgreSQL: pg_upgrade 9.5 to 13 failing with “connection to database failed: FATAL: database ”template1“ does not exist”
I'm trying to upgrade a 9.5 server to 13 but the pg_upgrade process is failing after the main checks. Server is running CentOS 8 on EC2 and is using the pgdg repo installs of each server.
bash-4.4$ /...
0
votes
0answers
15 views
Pg_rewind Uitility - PostgreSQL Snapshot standby [closed]
We have Primary-secondary PostgreSQL Streaming replication.
I have done some testing.
Steps.
1.Promote secondary
2.Run some DML on secondary which is in Write mode(another new primary)
3.Down new ...
0
votes
2answers
147 views
How to allow in-progress transactions to complete when shutting down PostgreSQL?
I'm working on automating as much as possible the mechanics of resizing an EBS volume on which we store all databases. We're going to host a large number of large-but-infrequently-used databases on a ...
0
votes
1answer
18 views
What is difference between START WAL LOCATION, STOP WAL LOCATION, and CHECKPOINT indicated in the backup history file?
I am a bit confused about these locations. Could someone give a short definition for these locations.
2
votes
2answers
116 views
Enforce that every company has at least one CEO
I've a requirement to ensure that every company has at least one CEO.
Example database model with requirements db<>fiddle.
Basically, a CEO can't be removed from a company without naming another ...
0
votes
1answer
27 views
Postgresql Physical Replication vs Logical Replication?
I have reading about difference between logical replication slots and phyiscal replication slots in Postgres. I am confused about some paramters and how they are used.
For a replication slot, we have ...
0
votes
1answer
27 views
Why can't PostgreSQL identity columns be used in CREATE TYPE?
My impression is that each PostgreSQL table has a corresponding composite type associated with it. But it seems that the PostgreSQL identity column cannot be used in a CREATE TYPE statement:
=> ...
0
votes
1answer
4k views
postgresql recovery after reboot
I installed Postegresql and added a database which I was using as part of a local server. However, I restarted the computer and my Postgresql database seems to be gone. The Postegresql documentation ...
19
votes
4answers
2k views
Is there a common pattern for storing many boolean flags other than adding a ton of columns?
I have a bunch of booleans I need to associate with user accounts. They need to be toggled true and false, though not particularly frequently - they're going to be read more often than they're written....
2
votes
2answers
497 views
How to dump a postgreSQL/postgis sample database filtered by a spatial extent?
My goal is to extract a subset of my postgres/gis database to create a sample dump which has to have the same data model with relations between tables and views, but which has a smaller spatial extent....
1
vote
1answer
38 views
Quickly fetch rows based on bits
I'm dealing with slow queries due to the sheer amount of possibilities. I'm looking for a better solution or help with the solution I already have in motion.
Background Context:
We have people in our ...
0
votes
1answer
33 views
Same query shows significant differences in performance on different data [closed]
I'm having a problem with writing a Postgres query that really appreciates anyone's help. I have 3 tables related to each other as below:
Order:
id (pk)
updated_at (datetime)
owner (foreign key, to ...
0
votes
1answer
259 views
Fetch 10 paginated ranked posts of each category
Posts table as p:
| id | cid | score | ...
--------------------
| 1 | 1 | 3 | ...
Categories Table as c:
| id |...
--------------------
| 1 |...
What i need? Reterive paginated ranked ...
0
votes
1answer
625 views
Stop pg_restore from returning false errors?
How do you stop pg_restore from returning an error code if it encounters a meaningless non-error like “schema "public" already exists”?
I'm trying to automate a database transfer, and ...
1
vote
0answers
20 views
PostgreSQL does not start: FATAL: xlog flush request is not satisfied — flushed only to 0
I have a Docker PostgreSQL (TimescaleDB) developer instance with a local Linux volume mapping.
version: '3'
services:
dex-timeseriesdb:
image: timescale/timescaledb:latest-pg12
# https://...
0
votes
0answers
15 views
AWS-RDS database catalog is restricted
I use PostgreSQL 13.2 in AWS-RDS.
The database is up and working properly; I can connect with the user provided by Amazon.
We need to alter the catalog to enable the implicit cast of integer to ...
0
votes
1answer
346 views
Can any one Explain me What is the Use of Cascading Replication in Postgresql?
What are the advantages of cascading replication in PostgreSQL.
I have done Testing On this With three nodes.
M1---->S1----->S2
What is the Use if My master(M1) goes Down(failover). Where the ...
4
votes
2answers
2k views
Create JSON object from recursive tree structure
Having this simple many-to-many self-referential structure.
An item owns other items through the joins table:
CREATE TABLE items (
item_id serial PRIMARY KEY
, title text
);
CREATE TABLE ...
3
votes
2answers
508 views
Marking duplicate records
I have a list of things, some of which are duplicates.
create table things (
id int primary key,
title text not null,
author text not null,
duplicate_of int null references things(id)
);
I'...
0
votes
1answer
4k views
Visual Studio Code Postgres SQL Connection
I would like to use Visual Studio Code to preform my PostgreSQL queries, and general DB maintenance as an alternative to pgAdmin4. I have read the documentation included with the appropriate extention ...
1
vote
1answer
38 views
Can PostgreSQL build an index without storing the source data?
In Elasticsearch we can configure an index to not store the source data. Is it possible to recreate this in PostgreSQL?
Today, we are building an application that encrypts all (sensitive) data before ...
1
vote
1answer
336 views
Robust way to build a DWH without downtime
I have a process that build a data warehouse using Postgres. The process runs every day at midnight and works in three steps:
Copy the production database (prod) to a temporary database (dwh_tmp) ...
