Questions tagged [postgresql]
PostgreSQL is a community developed, open source, relational database, made available under a BSD-like license. The PostgreSQL documentation is expansive and incredibly useful.
568 questions
0
votes
1
answer
17
views
DAViCal: After rebuilding server, peer authentication failed for user davical_app
I have just rebuilt a server with PostgreSQL and DAViCal.
I added the following lines to pg_hba.conf (same config as on the old server):
local davical davical_app trust
local davical ...
- 1,122
0
votes
0
answers
23
views
What is wrong with the function
CREATE FUNCTION MAX(v1 integer, v2 integer)
RETURNS integer AS $$
BEGIN
IF (v1>v2) THEN
RETURN v1;
ELSE
RETURN v2;
END IF;
END;
$$ LANGUAGE plpgsql;
Error message:
ERROR: syntax error at ...
- 3
-1
votes
1
answer
75
views
syntax error at or near "GENERATED"
Please somebody inspect my code to see why I get syntax error
TABLE:
CREATE TABLE post_partitioned (
pk integer,
title TEXT,
content TEXT,
author INTEGER,
category INTEGER,
reply_to INTEGER,
...
- 3
0
votes
0
answers
97
views
Why can't I seem to install PostgreSQL on Windows 11?
I want to update my PostgreSQL version from 16.2 to 17.
I have uninstalled the old version, removed postgre folder, and deleted local temp. But, every time I install new version, I found this error.
...
- 1
0
votes
0
answers
24
views
Is it possible to enable hugepages for both postgres and another service?
Suppose I have a instance of postgresql server v16 with 10GB of shared buffers. Having default hugepage size equal to 1gb, I need 11 hugepages for postgresql and works fine.
How should I configure ...
- 121
0
votes
0
answers
33
views
Podman container with mediawiki can't connect to mariadb
I have a podman container with a mediawiki inside and a mariadb natively installed. So far it is running and I can access the web-interface.
But when I try to connect to the database during the setup ...
- 113
0
votes
0
answers
27
views
How to compare the disk size and the database size in Zabbix?
I am going through scenario where the Postgresql database is growing very fast and it could rich the whole disk space.
So I suppose there is a possibility to use a Zabbix trigger to compare those two ...
- 535
0
votes
0
answers
20
views
Can't start/connect postgresql
I don't understand but when I noticed postgresql seemed to not working like usual, and I've been dealing with this issue, but can't see the problem. Thank you for your help.
journalctl:
$ sudo ...
- 188
0
votes
0
answers
33
views
Remove the severity of the error from error message send by Postgresql
Is there a way to remove the "ERROR:" from the error message that postgresql sends? Adding a sample error message :
ERROR: insert or update on table "bonus" violates foreign key ...
- 101
2
votes
0
answers
90
views
Will DNF also upgrade my installation of PostgreSQL 16?
I have just installed PostgreSQL 16 in Fedora 40 with the following command as per this page:
dnf install postgresql-server postgresql-contrib
According to the main PostgreSQL roadmap, version 17 is ...
- 379
1
vote
0
answers
72
views
Docker how to overcome "change of ownership" error of a folder from an external mounted SSD drive
I have a question regarding ownership mapping of folders via the Docker-Compose file.
Shortly to my problem:
I'm using Docker on my RaspiPi 4 and wanted to enter the world of paperless office (...
- 11
0
votes
0
answers
49
views
What are the command line parameters that I can pass to the PostGIS installer?
I want to silently install PostGIS on a Windows machine with PostgreSQL using Ansible.
To do so, I need to be able to run the PostGIS installer on the commandline, with the required options as ...
- 5,816
0
votes
1
answer
1k
views
Kali Linux Postgresql Metasploit DB Fails to Start
I currently have 3 versions of postgres installed, 14, 15, 16. All 3 are actively running and can be accessed from user postgres on ports 5432, 5433, and 5434.
When running sudo msfdb status it ...
- 1
0
votes
0
answers
12
views
Connection error whe configuring postgresql_basebackup in nxs-backup
when configuring postgresql_basebackup in nxs-backup I encountered the following:
PSQL connect error: pq: database "repluser" does not exist
Tried to edit pg_hba.conf, but it still doesn't ...
- 1
0
votes
0
answers
14
views
kubernetes pod won't start unless I rename the azure disk
I encountered a strange behaviour concerning my Azure kuberentes pod.
This is a postgres db disk.
My pod crashes only if I choose a specific (the original) name for the disk
Using the same snapshot by ...
- 101
0
votes
0
answers
455
views
I installed Postgresql. While trying to create user in psql, I messed up the configuration settings. Now, I can't even uninstall or install completely
W: An error occurred during the signature verification. The repository is not updated and the previous index files will be used. GPG error: http://apt.postgresql.org/pub/repos/apt jammy-pgdg InRelease:...
0
votes
0
answers
175
views
An error occurred while reading data from the provider: The remote certificate is not valid according to the validation procedure, PowerBI Postgresql
I wanted to use PowerBI to view and manage things on my Postgresql Database server, but when I select obtain data from another source and select Postgresql DB and enter the credentials (server, DB ...
0
votes
0
answers
21
views
PostgreSQL - Replacing pg_dump -o parameter when migrating to newer PostgreSQL version?
The pg_dump -o parameter was removed in PostgreSQL 12. What should I replace it with when migrating from PostgreSQL 9 to 14?
-o
--oids
Dump object identifiers (OIDs) as part of the data for every ...
- 213
1
vote
1
answer
341
views
Issue when creating a new user and group on macOS
I need to create a user and a group called postgres on my mac but I seem to have issues with that. The reason for needing this user is because I've installed postgresql@15 via brew and it created a ...
- 113
0
votes
0
answers
48
views
Postgresql on Linux Mint 21.3 can not create server
I downloaded the desktop version of postgresql on my linux mint 21.3 successfully (over Ubuntu Jammy Download-Method) but it still seems to have a problem with the connection.
postgresql connection ...
- 1
0
votes
0
answers
46
views
granting privileges to user in postgresql
I'm trying to setup a new project with a Postgresql 15.5 database.
I have a running instance. If I create a new database
psql --command "create database \"tagger-db\""
create a ...
- 101
0
votes
0
answers
198
views
Why Postgresql latest stable release is not in Debian stable repository yet?
I can see that today v15 is the stable one version for Postgresql on Debian 12 (here: https://packages.debian.org/bookworm/postgresql).
But v16 is the latest one Postgresql stable released on 2023 ...
- 121
0
votes
1
answer
392
views
PostgreSQL traffic analysis - Wireshark - how many bytes in total are being received by the client?
<TL;DR>
How many bytes are being received in total when a SELECT * command is issued for a (small) table?
</TL;DR>
I have a PostgreSQL table - very simple (see fiddle here):
CREATE TABLE ...
- 101
0
votes
0
answers
123
views
Data was reset from the postgresql table after deleting the Packages file
I created my project in python using postgresql on windows in a wsl environment. And accidentally deleted the file at the address Appdata/Local/Packages. After that, everything broke down and the ...
- 1
0
votes
0
answers
243
views
Pgbouncer: Multiple Instances on same host receiving same TCP connection when using SO_REUSEPORT
I was able to setup two pgbouncer instances for database connection pooling, on a single VM (CentOS 7.0) listening to same port (6432) on two different TCP socket (I believe) using Linux Kernel ...
- 67
2
votes
1
answer
197
views
Could interrupting a SQL query that was taking too long lead to a decrease in disk space?
For a personal data analysis project, I was trying to find the most frequent 3 products that were being sold together by doing a self join (twice).
As the query was taking over 15 mins and the laptop ...
- 23
1
vote
0
answers
154
views
How do I remove the postgres user
I am on a windows 10 computer, and am looking to clean up my system as I have had this machine for a while, and it has quite a bit of "junk" on it.
I believe part of this is the postgres use ...
- 176
2
votes
2
answers
180
views
Unknown commands hogging all my cpu. " ./4 -c/tmp/ ..."
this is a follow up for this issue
Usually, a few hours after a restart we would get a weidly named process like this
htop output
Turns out it was from out docker postgresql service and the posgres ...
- 21
0
votes
1
answer
83
views
database connection error with perl app (rt 5 - request tracker) connecting to Postgres database
On a CentOS 9 system I installed postgres via dnf
# dnf info postgresql
Last metadata expiration check: 3:26:33 ago on Tue 21 Nov 2023 12:13:48 PM PST.
Installed Packages
Name : postgresql
...
- 11
0
votes
1
answer
88
views
how to optimize DB query for table with millions of rows when info is sorted (sorted by date in this case)
Imagine you are chatting with your SO on Whatsapp and the conversation is years long, so there are thousands if not millions of messages.
CREATE INDEX date_index ON messages_table (date_column);
and ...
- 269
0
votes
1
answer
46
views
PostgreSQL keeps drive active. How to debug?
I run a small home server with Debian including a autoshutdown script if the system is idle. Since a few days my drives are not staying in standby and with this the server is always running.
I ...
- 41
-1
votes
2
answers
689
views
postgresql sevice will not start on aws linux 2023
I am trying to install, configure, and start a postgresql 12 service on amazon linux 2023. I have installed the service from source and have created a postgresql.service file in /usr/lib/systemd/...
- 229
2
votes
1
answer
165
views
PGAdmin how to open straight tables submenu
Is there a way how to open straight tables submenu in PGAdmin? Actually if I want to see table in some database I need to click on
database->schema->public->tables
It's 4 clicks till I see ...
- 365
0
votes
0
answers
776
views
How to make latest PostgreSQL version upgrade the default version in Ubuntu Focal Fossa
Beforehand, I will admit that I am quite frustrated after attempting to upgrade my version of PostgreSQL-12 to PostgreSQL-16 in my virtual machine (Windows Subsystem for Linux2 Ubuntu 20.04.6 LTS ...
0
votes
1
answer
1k
views
Stack builder for postgresql does not list local installation
As per the pgAdmin site, the pgAgent can be downloaded using EDB's Stackbuilder.
I have postgres 15 running and pgAdmin 4 can connect to it and there is a database and everyone's happy..
However, I am ...
- 101
0
votes
1
answer
4k
views
How do I set up a postgresql database in Windows Powershell?
I'm attempting to follow a guide to setup a postgres database using a postgres sql file on my computer. The instructions I'm trying to work off of are below:
Create the database. This will create a ...
1
vote
0
answers
599
views
psql hangs on connect over SSH tunnel proxy
I am proxying my connection to a PostgreSQL server through an SSH tunnel.
[laptop] ---(ssh)--> [arbitrary linux server] --(TCP 5432)--> [postgresql server]
ssh user@arbitrarylinuxserver -L5432:...
- 161
0
votes
1
answer
297
views
How to count and export duplicate values (example attached)?
I have been scratching my head trying to figure out how to produce a statistic table as formulated in the attached picture:
I have about 670k objects (Item_id) divided between around 1000 IDs (FID) ...
- 101
4
votes
2
answers
15k
views
Postgres docker error "data directory "/var/lib/postgresql/data" has wrong ownership"
I am trying to start a postgres container using docker
I have tried using multiple versions of postgres docker images
I have tried using volumes as mentioned here
tried setting POSTGRES_USER ...
- 80
0
votes
1
answer
72
views
Accidentally installed postgres10 and postgres12 on Ubuntu
Ubuntu 20.04 came preinstalled with postgres10. Roughly 2 years ago I did a sudo apt-get install postgresql which must have installed postgres12. I never noticed that when I sudo -u postgres psql (...
- 101
0
votes
1
answer
283
views
PostgreSQL ODBC Driver for IBM i Compile Error
I am attempting to compile the PostgreSQL ODBC driver on our IBM i system, and I'm getting a strange error. The version I'm trying to compile is 12.02, since that's the version of PostgreSQL we have ...
- 113
1
vote
0
answers
470
views
Is there any way to pg_upgrade PostgreSQL 12 to 15 over network?
My situation is a little bit complicated.
I have an older version of PostgreSQL 12.9 installed on an older Centos Stream 8 machine. There are about five custom databases created under the roof, say ...
- 149
1
vote
0
answers
191
views
Postgres' pg_total_relation_size returning unexpected table size for a zfs-based tablespace
I have Postgres 15.1 running on Ubuntu 20.04. Basic FS is ext4, but I have mounted zfs onto a sparse file like this (for compression testing purposes):
truncate -s 100G zfspart.dat
zpool create -o ...
- 111
2
votes
0
answers
486
views
Vagrant - Ansible - Install PostgreSQL and Postgis
I'm trying to install PostgreSQL and Postgis with Ansible on a Vagrant VM.
The VM is only use locally for local development, so that's why I grant access to databases.
But I'm reaching some issues to ...
- 21
0
votes
0
answers
166
views
How to create backup in directory format
Creating backup in directory format using
pg_dump -f "sba" -Fdirectory --jobs=32 --verbose sba
throws error
pg_dump: error: could not stat file "sba/282168.data.gz": value too ...
- 179
1
vote
2
answers
2k
views
PyQt6 QPSQL Driver not loaded
I'm trying to use the SQLTableModel from PyQt6 (installed with pip) in Python 3.11. For this I need to connect to my Postgresql 13 database (hosted on AWS) using the QPSQL driver from PyQt6.
The ...
- 11
1
vote
0
answers
32
views
How would I keep a live database directory atomically synchronized with other local disks on Windows 10?
I run PostgreSQL 14.1 on Windows 10 (Pro). The computer has multiple internal storage disks. The PostgreSQL "data directory" is set to C:\whatever\PGDB.
I do make full backups daily, but ...
- 11
0
votes
2
answers
509
views
Postgresql :: operation select sysdate didn`t work
I recently installed postgres v15 in windows 10 machine and below paths set to system variables >> Path
C:\Program Files\PostgreSQL\15\lib
C:\Program Files\PostgreSQL\15\bin
When I run SELECT ...
- 1
3
votes
1
answer
2k
views
PostgreSQL cluster won't start, no log of the issue, message on WAL redo
I'm having trouble with a system that I didn't set up or configure myself, but pieces of the system seem to generally be in more or less default places. My current theory is that the PostgreSQL ...
- 192
1
vote
2
answers
747
views
How to recover Postgresql databases from an older version
I was running for many years Davical on a Debian server (Debian version 11.6 at the moment) to have access to my calendar from many different devices. That application uses PostgreSQL to store its ...
- 121