Questions tagged [postgresql]
All versions of PostgreSQL. Add an additional version-specific tag like [postgresql-13] if that context is relevant.
13,441
questions
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 ...
