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
17 views

how to connect to PostgreSQL using a stable way

Now I am install PostgreSQL 13 in remote cloud server and expose the 5432 port to public network. I add a whitle list in PostgreSQL config file pg_hba.conf like this: host all all ...
0
votes
0answers
20 views

Remove columns from denormalized db and still have same results

I have a database where are stored car brands and their models. Brands | id | name | compare_to_brand_id | |----|------|---------------------| | 1 | Fiat | NULL | | 2 | Ford | 1 ...
0
votes
0answers
11 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
0answers
6 views

BUCARDO - PL/Perl function “validate_goat” at /usr/local/bin/bucardo

I'm new to Bucardo, I have been trying to replicate PostgreSQL-12 using Bucardo with no success due to the errors. DBD::Pg::st execute failed: ERROR: DBI connect('dbname=test1','bucardo',...) failed: ...
0
votes
0answers
9 views

find union of generate_Series() with other date column

i have a table and i want to apply union of dates column to generate_series for each userId. activitiestable :- | userId | dates | | 2 | 2021-06-21 00:00:00| | 2 | 2021-06-22 ...
0
votes
0answers
14 views

why postgresql data files differ in size significantly

My data files (including index files) of PostgreSQL locates in the default table space (i.e., base/). ls -l postgresql-data/base drwxr-x--- 2 postgres postgres 4096 1 drwxr-x--- 2 postgres postgres ...
0
votes
1answer
14 views

removing dates from generate_Series in postgreSQL

im trying to remove some dates from generate_Series in postgres liie this:- select min(dt)+'1 day' from generate_series(date_trunc('day',now()) , date_trunc('day',now()+'30 day'),'1 day') as s(dt) ...
0
votes
1answer
12 views

Is it possible to do WAL physical replication to a db without making that db read-only?

(Novice question. Please let me know how I can improve the question, or if I need to delete) I want to create a separate database so I can run more expensive queries on it. This database should be a ...
-3
votes
1answer
39 views

SQL Server to PostgreSQL equivalent

I need some help in converting or equivalent of below in postgreSQL.. From MSSQL code to postgreSQL:- CONVERT(bit,CASE WHEN column_a & 1 > 0 THEN 1 ELSE 0 END) as ABC CONVERT(tinyint,CASE ...
0
votes
1answer
22 views

How to use multiple SELECTs within a single INSERT?

I'm currently working with PostgresSQL 13.3 and I thought I could have a file with something like this: INSERT INTO points (zone_id, element_id, position_id) VALUES (SELECT zones._id, elements._id,...
0
votes
0answers
15 views

Join on Jsonb Path Query with filter condition

I have a jsonb column with rows like this. "regions" array contains up to 60 elements. All rows contain the same list of ids but 'result' differs. { "a": [ { &...
18
votes
3answers
2k views

Why is the default page size for databases so small?

In PostgreSQL and SQL Server, the default page size is 8 KB, in MySQL is 16 KB and in IBM DB2 and Oracle it is only 4 KB. Why are these page sizes so small? Is there a historical or memory usage ...
0
votes
0answers
45 views

Help with database design for player inventory [closed]

I need some help with designing player inventory database. Player has one inventory (so player_id can be used in relations). Inventory contains Items of some types: Passport or Wearable. Items of the ...
1
vote
2answers
95 views

How can a cheap function in a SELECT make the whole query slow?

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
71 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
28 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
49 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
58 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
31 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
24 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
20 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
25 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 ...
0
votes
0answers
14 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
57 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
28 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
16 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
14 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
27 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
31 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
47 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
21 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
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
0answers
13 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
108 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
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,...
1
vote
1answer
36 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
13 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
32 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
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+...
3
votes
1answer
101 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
28 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
49 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
2 3 4 5
269