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.
552
questions
0
votes
1
answer
7k
views
How to delete a row with pgAdmin III?
For something so obviously obvious I just can't do it at all.
How can I just delete one row without doing the command manually.
0
votes
0
answers
25
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 ...
0
votes
1
answer
51
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 ...
1
vote
1
answer
4k
views
PostgreSQL 9.2.4 (Windows 7) - Service won't start, “could not load pg_hba.conf”
I am trying to get Postgres 9.2.4 to run as a service on Windows 7. After installing postgres, the service was running fine. However, after setting postgres up as a server for another program, the ...
-2
votes
0
answers
20
views
connect Pgadmin with the server of company
I've installed OpenCVE on my server session and have extracted the data into a .sql file. However, the data is in JSON format and I want to connect PgAdmin to my session to view all the data, tables, ...
1
vote
1
answer
52
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 ...
5
votes
1
answer
24k
views
How can I completely uninstall pgAdmin3 from OS X?
My pgAdmin3 install has run into a problem (windowing glitch that renders it unusable), and I want to try completely removing it from my system and installing it freshly to see if that resolves the ...
0
votes
1
answer
6k
views
Copy few rows between databases via dblink in PostgreSQL
I would like to copy few rows between my PostgreSQL two databases (A and B) which are in one local server.
I know it is probably possible via dblink extension, but I haven’t found the answer how to ...
0
votes
0
answers
67
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 ...
0
votes
0
answers
47
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 ...
1
vote
1
answer
4k
views
Why can't I connect to PostgreSQL (localhost server) when my VPN is active?
I just wasted an hour of my life trying to figure out why pgAdminIII couldn't connect to the server on localhost. After trying config edits, starting/stopping the postgresql service, etc., I finally ...
3
votes
1
answer
9k
views
Make Wine run its applications as user profile, not Administrator
For a very specific case, I need to install PostgreSQL under Wine to run a Windows application that packs it's own installation of PostgreSQL even if PostgreSQL is already installed, for security ...
3
votes
2
answers
6k
views
Why is my PostgreSQL startup service not working properly until I restart it manually after reboot?
I have PostgreSQL running on my Linux server.
PostgreSQL is enabled and is active after reboot.
In the config file from PostgreSQL I enabled remote access. But every time after I reboot my device, the ...
2
votes
1
answer
172
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 ...
-1
votes
3
answers
5k
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
2
answers
71
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 ...
1
vote
0
answers
56
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 ...
0
votes
1
answer
40
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
...
0
votes
1
answer
44
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 ...
1
vote
1
answer
112
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 ...
0
votes
0
answers
27
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 ...
-1
votes
2
answers
337
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/...
0
votes
0
answers
434
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
524
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 ...
0
votes
2
answers
1k
views
/usr/local/opt/postgresql/*.plist no match
i'm trying to install postgres on my macOS (Monterey 12.0.1).
i've ran brew install postgres and the next step is to run ln -sfv /usr/local/opt/postgresql/*.plist ~/Library/LaunchAgents, but when i do,...
0
votes
0
answers
247
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 ...
0
votes
1
answer
2k
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
346
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:...
0
votes
0
answers
57
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
187
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 ...
1
vote
2
answers
956
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 ...
0
votes
1
answer
209
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) ...
0
votes
0
answers
224
views
How do I install pgaudit on PostgreSQL 11.5 in Red Hat Linux environment?
PostgreSQL 11.5 was installed with Control-M in a Red Hat Linux environment. Unfortunately, there are only 2 extensions (dblink, plpgsql) that were a part of that installation.
I am new to PostgreSQL. ...
4
votes
1
answer
9k
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 ...
0
votes
1
answer
60
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 (...
2
votes
0
answers
2k
views
How create new server connection in pgAdmin 4 by pasting PostgreSQL connection string (URI)
See https://stackoverflow.com/a/20722229/470749
It's very common for server connections to be specified in just one URI, which is more convenient than needing to type out a bunch of values into ...
1
vote
0
answers
361
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 ...
1
vote
0
answers
102
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 ...
2
votes
0
answers
369
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 ...
1
vote
1
answer
7k
views
PostgreSQL getting error during pg_restore using pgadmin pg_restore: [archiver (db)] could not execute query: ERROR: role does not exist
I am trying to restore a database from one schema to another schema in PostgreSQL using pgadmin backup and restore utility.
The backup is successful for the database A but when I try to do the restore ...
0
votes
0
answers
137
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 ...
1
vote
2
answers
23k
views
How can I install PostgreSQL using the command prompt?
I have problems with installing PostgreSQL. I tried to download PostgreSQL Version 9.6.1 for Windows x86-64 from enterprisedb.com.
I have the widespread problem, that the prompt "Failed to load ...
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 ...
3
votes
1
answer
924
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 ...
0
votes
2
answers
300
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 ...
0
votes
1
answer
407
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 ...
1
vote
1
answer
704
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?
1
vote
0
answers
516
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 ...
0
votes
2
answers
196
views
Having problems logging in into a new Postgre installation
I just started taking a course which includes PostgreSQL. I am running Windows10 x64 on my machine.
I installed Postgre with the graphical installer. After having to solve many problems (like could ...
2
votes
3
answers
6k
views
Why does my terminal start up with "(eval):export:1: not valid in this context"?
When I open my terminal, it shows up with the error
(eval):export:1: not valid in this context: Fusion.app/Contents/Public:/Applications/Postgres.app/Contents/Versions/latest/bin
I can't figure out ...