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.
569 questions
0 votes
2 answers
90 views
Is there a MS SQL wrapper / converter interface for a Postgresql database? [closed]
This is kind of an exotic situation: We have a very specialized and old .NET application (without sources) that connects to a MS SQL Server database. As there are certain restrictions for the "...
- 610
3 votes
1 answer
214 views
Systemd postgresql service version
I'm coding a middleware connected to an old PG server, so I have a dependancy in my middleware.service says it requires/start-after "postgresql.service" For now, I'm busy to upgrade to PG17, ...
- 197
3 votes
2 answers
377 views
Install postgresql-18 beta from apt repo on debian
Postgresql-18 beta is out, there are lots of packages for postgresql-18 in the debian apt repo, but postgresql-18 has no installation candidate. I assume this is to prevent accidental installs before ...
- 33
0 votes
0 answers
61 views
How to use pg_dump with a client certificate on Windows Server?
Postgres 17 server certificate is issued by RapidSsl for server.mydomain.com This is validated by DigiCert Global Root G2 certificate. PostgreSQL 17 client certificate is created using this tutorial. ...
- 253
1 vote
0 answers
1k views
PostgreSQL driver files missing for dBeaver
I have dBeaver Community edition installed on a VM and am trying to create a connection to a PostgreSQL database on that VM, but when I try to test the connection, it says that there are some ...
- 584
0 votes
0 answers
298 views
Postgres data directory not found
After rebooting my server, postgres service was not starting. On checking the event viewer I see that it is trying to locate the data at the wrong place. The service is defined as "C:\PostgreSQL\...
- 1
0 votes
0 answers
87 views
Why won't my Homebrew PostgreSQL service run on macOS on an M4 Mac?
I cannot for the life of me figure out why my Homebrew PostgreSQL service won't run. No matter what I do I get this: % brew services restart postgresql Stopping `postgresql@14`... (might take a while) ...
- 131
1 vote
0 answers
66 views
How does pgAdmin from Windows automatically start Postgres server in WSL?
I have installed Ubuntu with WSL2 on my Windows 11 machine. I have a postgres DB in the WSL and can access it with pgAdmin from Windows. When I try to connect pgAdmin to the database without logging ...
- 111
1 vote
0 answers
41 views
Updating Postgres Image Causes Other Containers to Use Untagged (<none>) Image
Hello Docker Community, I’m facing an issue when updating a shared postgres:16 image used by multiple containers. Here’s the problem: Scenario: Multiple containers rely on the same postgres:16 image. ...
0 votes
0 answers
315 views
How to map a port to a local UNIX socket running as certain Linux user, e.g., PostgreSQL?
With command line, I will login to the PostgreSQL host through SSH, and call sudo su - postgres, then call psql as the PostgreSQL superuser to connect through the UNIX socket at /run/postgresql/.s....
- 43
0 votes
0 answers
109 views
How to fix PostgreSQL collation issue related to system configuration on Red Hat Enterprise Linux 9?
With PostgreSQL 16 running on a server of Red Hat Enterprise Linux 9, we got the following error when trying to create a database with collation in Canadian en_CA.UTF-8. However, on a local virtual ...
- 43
0 votes
0 answers
226 views
Receiving error message user_management.index when visiting pgadmin /browser after login
I followed the documention to install pgAdmin4 in server mode on a Debian 12 system. I used nginx and gunicorn. nginx was installed from the Debian repos. The rest was setup inside a virtual ...
- 765
0 votes
1 answer
131 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,152
0 votes
1 answer
58 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 ...
- 5
-1 votes
1 answer
573 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, ...
- 5
0 votes
0 answers
506 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
231 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
2 votes
1 answer
305 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
357 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
1 answer
2k 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
761 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:...
1 vote
1 answer
661 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
73 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
1 answer
600 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 ...
- 123
0 votes
1 answer
756 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
367 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
233 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
285 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
282 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
144 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
244 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 ...
- 279
0 votes
1 answer
67 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
827 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
233 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 ...
- 397
0 votes
0 answers
914 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
2k 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
6k 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
821 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
367 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
3 answers
20k 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
85 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
354 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
532 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
265 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
524 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
199 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 ...
- 253
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
40 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
767 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
3k 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