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
0
votes
1answer
19 views

One-row postgres query as CTE/subquery much slower when passing subquery field into function / maybe related to inlining?

I'm using postgres 13.3 with inner and outer queries that both only produce a single row (just some stats about row counts). I can't figure out why Query2 below is so much slower than Query1 (they ...
4
votes
1answer
55 views

Ordering rows by repeating pattern

Given a table in Postgres like CREATE TABLE t1 ( type text NOT NULL, value int NOT NULL ) where type could be A, B or C and given an arbitrary, repeating pattern like A B B C B B how can I query ...
0
votes
0answers
26 views

Can't create a Postgres extension for pgaudit

I compiled pgaudit on Windows10 using Visual Studio. I added shared_preload_libraries = 'pgaudit' and restarted the server. When I try to add the extension, I get: ERROR: could not find function &...
0
votes
1answer
45 views

Using subquery in WHERE makes query extremely slow

I have this rather basic query that is very slow for reasons I can't figure out: SELECT s.id FROM segments s WHERE ST_DWithin( s.geom::GEOGRAPHY, ST_Envelope((SELECT ST_COLLECT(s2....
2
votes
1answer
53 views

Disabling triggers makes DELETE fast, but the table has no triggers?

I am deleting 130k out of 750k rows from a PostgreSQL table. The first time, it took 8 hours to complete the delete query. Second time, I added an index to the table, and rebuilt that index. Now it ...
0
votes
1answer
26 views

Look for a list of specific characters using RegEx

I'm trying to write a query (Postgres 8.2.15) which is checking whether the value in column1 consists of: English alphabet only (A-Z), there should be no specific characters with umlaut/ablaut coming ...
0
votes
0answers
23 views

How to restore a standalone server from a pg_basebackup using the fetch option

I am attempting to make a backup & restore of a production Postgres server (9.6 Windows) for development testing. This server is standalone and does not need to do streaming replication. I have ...
1
vote
0answers
19 views

Postgresql stale result for insert into select

I've noticed when two processes (let's call them A and B) insert a record into the same table in Postgresql and don't commit those changes, the 2nd (later process, B) blocks until the 1st process (A) ...
0
votes
0answers
24 views

Postgres locking issue

I have the following Postgres table: CREATE TABLE my_db.url_data ( url varchar(2500) NOT NULL, status int, priority int, ... last_update_ts timestamp, CONSTRAINT url_data_pk ...
1
vote
1answer
24 views

Redshift - Insert data into a temp table with a condition being column doc_id should be unique and not null

I'm trying to create a temp table and insert data into it in Redshift. My goal is to create a single record for unique doc_id WHERE doc_id IS NOT NULL. Here's my code: -- Creating temp table to load ...
0
votes
0answers
13 views

Why is this logical replication slot using so much space?

I have 3 RDS postgres instances. Each has a single database, and in each database I've created a logical replication slot: SELECT pg_create_logical_replication_slot('test', 'wal2json'); Two of the ...
3
votes
0answers
49 views

Postgres - limit window function evaluation to once per partition

I have a table of daily stock quotes with ~40M rows and I'm trying to run a query that spits out the most recent date and the number of stocks that made a new one year high on that date. For example: ...
0
votes
1answer
26 views

PG_upgrade with Rsync 9.6 to 13 on Standby Server - Postgres password prompt issue

I am utilising the pg_upgrade documentation for 9.6 to 13. I can upgrade from 9.6 to 13 with the --link issue without issue on the master server. I have a slave machine which I want to upgrade, ...
0
votes
1answer
17 views

Can WAL files be read and modified at WAL_LEVEL = replica [closed]

There are multiple client DBs (one for each client) in our org. We have one backup server where all the client DBs are formed as schemas and stores the data to query for internal business purposes. ...
0
votes
1answer
15 views

PostgreSQL V13 Master-Slave Replication on Window Server 2016 Not Replicating

I have installed and configured PostgreSQL V13 on window server 2016 but replication is not working. I don't know where the error is because we are not facing any error regarding replication and ...
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
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 ...
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 ...
1
vote
1answer
44 views

Database is 300 GB, but I only see small tables?

I wonder why my database is so large. I type: SELECT pg_size_pretty(pg_database_size('defaultdb')); and it says defaultdb is 259 GB. That is huge. So I list my tables in psql with: \dt For each ...
0
votes
1answer
19 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
1answer
16 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
0answers
10 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 ...
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?
1
vote
1answer
96 views

Adding a WHERE clause slows down query

We run a database on PostgreSQL 12.6 on x86_64-pc-linux-gnu, compiled by Debian clang version 10.0.1, 64-bit We are trying to get the top rows of a result set where the most recent entry has a ...
0
votes
1answer
18 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,...
1
vote
1answer
35 views

Database architecture of high frequency sensor values

I have a lot of sensors (around 1000) and want to save their values. The sensors will mostly work around a sampling rate of 10Hz. Regular queries will be used for a live-view application. It can 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 ...
0
votes
2answers
31 views

How to set user-based .psql_history in linux?

If use postgres in linux, it has a postgres user. So when use psql from this user, it will generate a .psql_history file under this user - /home/postgres/.psql_history. When set many users on the ...
-2
votes
0answers
17 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+...
3
votes
1answer
99 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 ...
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
1answer
26 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
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.
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
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
3answers
48 views

Create unique index on Postgres with any column combination

I have a companies table: CREATE TABLE "companies" ( "id" bigserial, "name" varchar(255) NOT NULL PRIMARY KEY ("id") ) I want companies to have parents and ...
1
vote
0answers
19 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
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
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 ...
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 ...
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 ...
-2
votes
1answer
15 views

POSTGRESQL Query and insert based on results [closed]

In this case I'm trying to perform a SELECT based query followed by an insert of new records into the same table based on those results. For example a simple table: Table "samples" Fields: ...
-1
votes
0answers
26 views

Create Primary Key for Table [closed]

I am trying to identify a primary key but ended up having no idea. Can anyone please give me suggestions? I have the following table: racer_id | team_id | timestamp | races | time | typed | ...
0
votes
1answer
31 views

Create Postgres Index for OR query

This is the query I want to optimize: SELECT * FROM invited_member_events WHERE (user_id = '422820341791064085' OR inviter_user_id = '422820341791064085') AND guild_id = '...
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 ...
0
votes
1answer
21 views

How to list all sequences by psql with postgres?

I tried \ds didn't get any relations data. But I use SELECT * FROM information_schema.sequences; can get all the data. Why doesn't \ds work?
0
votes
1answer
24 views

Actual used and free space in WAL segments

I am new to the PostgreSQL world. I have an experience in administrating Microsoft SQL Server and Oracle databases. In SQL Server, we are able to find how much space is actually used in pre-allocated ...
2
votes
1answer
24 views

What are the possible meanings of NULL and zero for autovacuum_count, last_autovacuum, and autoanalyze_count in PostgreSQL?

I have a big table (let's call it big_table) in a PostgreSQL 12.7 database running on a Debian GNU/Linux server. The machine has 8 GB of RAM and 4 CPU cores, and mostly dedicated to this PostgreSQL ...

1
2 3 4 5
269