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.
560
questions
0
votes
0
answers
39
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
4
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
11
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
-1
votes
1
answer
150
views
Access denied when installing Postgres [closed]
When I try to install Postgres, I am receiving the following error of access denied even with my user being an administrator.
Access Denied
I want just to install postgres, i already installed it a ...
v1tor
0
votes
0
answers
79
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
81
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
15
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 ...
- 163
1
vote
1
answer
113
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
30
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
30
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
61
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
1
vote
1
answer
191
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 ...
- 111
0
votes
0
answers
90
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
116
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 ...
- 17
2
votes
1
answer
181
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
97
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
113
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
61
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
63
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 ...
- 271
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
0
votes
0
answers
45
views
Unable to connect to postgres installed as docker image in ubuntu vm in virtualbox from host psAdmin
i installed postgres on ubuntu vm on using virtualbox the host is windows 10
umen@umen:~$ docker ps
CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS ...
- 607
-1
votes
2
answers
523
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
135
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 ...
- 355
0
votes
0
answers
548
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
733
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
0
answers
317
views
Why is pure-ftpd-postgresql not connecting to my PostgreSQL database?
I am currently trying to set up a pure-ftpd server with PostgreSQL authentication, but I am unable to get my FTP server to communicate with my database during login.
For background, my setup consists ...
- 1
0
votes
1
answer
3k
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
454
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
0
answers
72
views
How to access the local files to restore in remotely accessed PgAdmin4 in centos 8
I have one Web Server with PHP and one Database Server with PostgreSQL 15. Both with Cent OS 8. I have installed pgadmin4 in the web server to access the Remote Database server through browser and it'...
0
votes
1
answer
250
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
1
answer
12k
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
67
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
250
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
406
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
134
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
435
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
144
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 ...
- 159
1
vote
2
answers
1k
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
30
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
376
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
1k
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 ...
- 141
0
votes
1
answer
528
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 ...
- 111
1
vote
1
answer
819
views
How to test in VSCode a postgreSQL SQL expression in Python's interactive notebook (= Jupyter Notebook, iPython) in a venv with package manager conda?
How to test in VSCode a postgreSQL SQL expression in Python's interactive notebook (= Jupyter Notebook, iPython) in a venv with package manager conda?
- 2,425
2
votes
0
answers
626
views
Azure database for postgresql: change the owner of a table without using the super user
I'm working on a PostgreSQL v10 Database created in Azure. This database has several users including:
myuser: user used by a web application. Has limited powers;
myadmin: admin user created by azure ...
- 385
-1
votes
3
answers
6k
views
running into permission issues when using the COPY command with postgresql 14.6-1 on linux 6.0.12-arch1-1
Hello everyone :) noob here,
trying to learn sql with postgresql 14.6-1 on arch linux kernel: 6.0.12-arch1-1 ,
the current topic of the book i'm reading, has me doing this command
COPY char_data_types ...
1
vote
0
answers
341
views
PostgreSQL sum function resurns different result when run multiple of times with psql
I have this query :
SELECT SUM(median) FROM my_table WHERE id = 100
-- median is of type float4
When I run from psql, it returns slightly different result for multiple of runs (For example 1000.08, ...
- 141
1
vote
1
answer
191
views
Persisting data for amazon ec2 instance running postgres
I am provisioning a cheap postgres instance for myself by installing directly on amazon ec2.
Everything will be deployed using terraform.
I would like the database data to persist between restarts / ...
- 133
0
votes
1
answer
484
views
Terraform setup of postgres conf files (pg_hba_conf + postgresql.conf) via User Data on AWS ec2 instance
I am setting up a cheap postgres server on amazon ec2, using Terraform.
I know I can add a User Data file when setting up a ec2 instance.
I know that with terraform, I can provide that User Data ...
- 133
1
vote
1
answer
1k
views
Connecting to a Postgres database, hosted within a Windows Subsystem for Linux, from a separate device
I have two devices:
HOST: a Windows device, on which I have installed a Windows Subsystem for Linux.
CLIENT: a Linux device.
I have then taken the following steps.
I have installed Postgres within ...
- 111
0
votes
1
answer
493
views
Importing a large .dump file into Postgres Database (Win 10)
I'm running into a problem where I want to import a really, really large .dump file (530gb) into a postgres database inside a docker.
This is the commmand I'm running inside Powershell:
cat .\dbexport....
- 175