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
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) ...