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.
234
questions with no upvoted or accepted answers
7
votes
0
answers
2k
views
Disable Linux Page Cache for Reads
Is there a way to turn-off page caching for reads in Linux? More specifically, I would like my processes to read directly from the disk.
The setting of my problem: I am trying out new query ...
7
votes
1
answer
2k
views
Can I configure PostgreSQL to reject SSL connections unless they are v1.2 or higher?
Can I configure PostgreSQL to reject TLS connections unless they are v1.2 or higher?
If so, can I get a doc link or instructions?
4
votes
1
answer
116
views
Does Postgres ever write to tables without file system timestamps getting updated for a long time?
I'm running Postgres 9.6 and backing it up once a while simply by stopping the cluster and using rsync on file level. One day I've recognized that some old, already backed up files for tables still ...
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 ...
3
votes
0
answers
2k
views
Postgresql 11 logical replication - stuck in `catchup` state
I'm running two postgresql 11 servers - master and slave (setup with logical replication).
The problem I'm facing is that today after weeks of uninterrupted work slave got out of sync with this error ...
3
votes
0
answers
622
views
Postgres files taking up lots of space
I have nearly 40G of files on my OSX, all under the following path :
/usr/local/var/postgres/base/9466989
36G total
1.0G 10107638.6
1.0G 10107638.5
1.0G 10107638.4
1.0G 10107638.3
1.0G ...
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
2k
views
How do I pipe 7za output directly to psql?
I have a Postgres database dump in a .7z archive.
I can extract it and import it with:
7za e dump.7z dump
psql -h localhost db_name < dump
but I'm just running out of disk space.
What is the ...
3
votes
1
answer
2k
views
How to use ShemaSpyGui for view database diagram
I installed Postgre today and I want to watch database shema.
I resolvse to use schemaSpy and download it and also schemaSpyGui
and then I see:
This is the command (password not displayed!) ...
3
votes
0
answers
3k
views
Installing and running two postgresql versions on different ports (or two instances of same server)
I have postgresql 9.1 installed on my machine (Ubuntu). I need another postgresql server that would run next to the old one. Exact version does not matter, but I'm thinking of using 9.2 version. How ...
3
votes
0
answers
826
views
Remove postgres from Mac that is installed in /usr/local/
I want to completely uninstall postgres and start from scratch - the version I have is refusing to work with PostGIS 2.0. I have read other answers on how to do this, but none of them seem to fit the ...
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 ...
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 ...
2
votes
1
answer
344
views
Postgres issues on mac. Want to clean up and do a fresh install
Am a noob to Mac OS and having trouble with Postgres on it. Initially installed Postgres 11 via the EnterpriseDB installer. Due to an issue with the system user name on mac having the '.' character, I ...
2
votes
0
answers
2k
views
how can I get pgAdmin4 to open as a standalone desktop application on MacOS?
I recently installed Postgres and pgAdmin4 and in it's default it opens on the browser. On PC I was able to "force" it to open as desktop app by entering the path to Chrome in the configuration popup ...
2
votes
0
answers
2k
views
docker-compose: no pg_hba.conf entry for host
I'm trying to make a few containers that are started by a docker-compose to work with a remote AWS database (managed by Heroku). I configured the envs as shown bellow:
environment:
- ...
2
votes
0
answers
730
views
SSH public key authentication between VMs not working for "postgres" user
I have a curious problem - very similar to the question here, but none of the answers have worked for me.
The problem
Public key authentication fails from the primary to the replica node. I used ssh-...
2
votes
0
answers
855
views
Uninstall PostgreSQL BigSQL Distro 10.5.1
Have installed the BigSQL PostgreSQL distro version 10.5.1.
Wanted to uninstall from a Windows 10 machine.
Using Add/Remove programs; error message on remove =>
Unable to locate uninstall.dat
I ...
2
votes
1
answer
274
views
Debian install of PostGIS extension is installing X packages
I'm trying to install PostGIS on a Debian server running Stretch. I installed PostgreSQL on it without any problems, but when I try to install the package postgresql-9.6-postgis-2.3, the latest ...
2
votes
0
answers
296
views
Allow remote connection to windows 8 postgresql from linux
I followed the instructions on this question and answer.
My C:\Program Files (x86)\PostgreSQL\9.4\data\postgresql.conf reads:
listen_addresses = '*' # what IP address(es) to listen on;
...
2
votes
0
answers
133
views
I'm trying to install PostgreSQL onto Ubuntu 14.04, but I am having difficulties after following a guide
I originally followed the guide at http://www.postgresql.org/docs/9.2/static/runtime.html. It instructed me to install postgres-xc, which is meant for making clusters of databases. On other sites, I ...
2
votes
0
answers
2k
views
Unable to Import Postgresql Database into Amazon AWS Database
I have a Postgresql 9.3 database locally which needs to be 'restored' to an Amazon AWS database. I am able to restore this database to various local Postgresql servers but on Amazon, using pgAdminIII ...
2
votes
0
answers
925
views
Could not open log file "pg_log/postgresql-Sun.log": No such file or directory
After installing postgresql on fedora 17 using the yum package manager. When I try to start it I get the error message.
[vagrant@example ~]$ sudo /sbin/service postgresql status
Redirecting to /bin/...
2
votes
0
answers
797
views
installing redis server on an ec2 instance where postgres is installed
I have an ec2 microinstance. I have installed only my postgressql database server over there.
I want to try redis for celery backend and also for some realtime value storing.
I am expecting as of ...
2
votes
1
answer
632
views
PostgresSQL created a user account on OSX
Hello I installed PostgresSQL on my macbook but when I restarted my macbook later I saw that instead of the usual User account and the Guest account I now have also a Postgres user in my startup page ...
2
votes
1
answer
2k
views
(-bash: initdb: command not found) When installing postgres mac
I get the following error (-bash: initdb: command not found) in the terminal when trying to install postgres on my mountain lion mac osx?
Also, does any one have a good resource for installing ...
2
votes
1
answer
2k
views
error creating database object: error verifying connection: dial tcp 127.0.0.1:5432: connect: connection refused
Looking to configure database secret engine in vault using postgresql.
Used docker-compose.yml to built vault and create postgres services.
both services are up and running and I was able to connect ...
2
votes
3
answers
1k
views
ERROR: could not load library "/usr/lib64/pgsql/hstore.so": /lib64/libc.so.6: version `GLIBC_2.14' not found
I’m trying to enable hstore on my PostgreSQL database running on an Amazon Linux AMI. I’ve already jumped through a ton of hurdles and ended up with this error that I can’t get past:
ERROR: could ...
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 ...
1
vote
2
answers
70
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
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 ...
1
vote
0
answers
345
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:...
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 ...
1
vote
2
answers
955
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 ...
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 ...
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 ...
1
vote
0
answers
275
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, ...
1
vote
0
answers
795
views
Postgres: Failed system call was shmget(key=3879116, size=56, 03600)
I recently upgraded from a 2013 Macbook Pro to an M1 iMac 2021. This was done using the data stored in Time Machine by my old Macbook. The old Macbook had no problems, ever, running Postgres. But then ...
1
vote
0
answers
693
views
Installing postgres on windows with locale
Disclaimer: as this should be a standard operation, i feel something on my side must have gone terribly wrong.
I am trying to use postgresql on windows 71. The installer from edb works fine, apart ...
1
vote
0
answers
374
views
Bash: read from socket file descriptor returns always the same data instead of blocking
I have a postgres started like this:
exec 3< <(su -l postgres -c "/usr/local/pgsql/bin/postmaster -p '$port' -d 3 -D '$backupDir' 2>&1 & echo \$!")
This redirects all logs ...
1
vote
0
answers
38
views
Issue with Computer Clock (or perhaps LAN)?
I am experiencing a very strange behavior for which I struggle to identify the source, but assume it's the computer clock. Here is the setup and synopsis:
I have two computers on my home network ...
1
vote
0
answers
2k
views
How can I allow postgresql connections from specific ip address?
I have a postgres database. I would like to allow connections to this database
from a specific machine.
I have installed postgresql-12 and have started the postgresql service on both machines.
I have ...
1
vote
2
answers
2k
views
How to backup and restore PostgreSQL instance, including role privileges?
I tried a bunch of different tutorials online, but they tend to omit the actual restore commands, or they reset role privileges back to nothing, or they use plain SQL backups in a way that errors (non-...
1
vote
0
answers
103
views
postgresql: can't select DB from windows with a restricted access setup
I have a remote machine, that has to access a DB on my server.
I explicitly granted access with:
host dbname dbuser 192.168.1.0/24 md5
and when testing from another linux machine with the ...
1
vote
0
answers
1k
views
`sudo service postgresql status` shows postgresql is running, but there is no process for the given PID when running `ps aux`?
It looks like it's running when I check the service status:
ubuntu@demos:/etc/postgresql/10/main$ sudo service postgresql status
● postgresql.service - PostgreSQL RDBMS
Loaded: loaded (/lib/...
1
vote
0
answers
524
views
Can't connect to virtualbox postgreSQL from host using navicat
I am currently trying to set up some databases using PostgreSQL 9.4 on a guest VM using VirtualBox running centos 6.5. I'm trying to connect to the database using navicat from the host but all it's ...
1
vote
0
answers
257
views
UTF8 error message in postgresql
I installed postgresql on windows using the guide on this page:
http://www.postgresqltutorial.com/
And I also loaded their dvdrental database from this site:
http://www.postgresqltutorial.com/...
1
vote
0
answers
1k
views
How to improve postgresql query execution time
Problem Statement:
we have a Enterprise product with Postgresql db as a backend. one of the tables has 5 columns and respective data type is (bigint time , bigint store_time , varchar category, ...
1
vote
2
answers
2k
views
SSL certificate error when connecting to PostgreSQL database with Oracle SQL Developer on Fedora 30
I have a postgresql 9.3 database running in a RHEL 6.2 kvm virtual machine. It's part of the application I support for work, and I cannot make any configuration changes to the contents of the VM ...