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
Filter by
Sorted by
Tagged with
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 ...
vahid's user avatar
  • 71
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?
brabster's user avatar
  • 335
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 ...
Thorsten Schöning's user avatar
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 ...
msba's user avatar
  • 41
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 ...
Greg0ry's user avatar
  • 228
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 ...
IanVaughan's user avatar
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 ...
Bruno Finger's user avatar
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 ...
Liam's user avatar
  • 3,060
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!) ...
gstackoverflow's user avatar
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 ...
Andrius's user avatar
  • 607
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 ...
Richard's user avatar
  • 163
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 ...
BobDev's user avatar
  • 21
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 ...
Ryan's user avatar
  • 1,934
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 ...
Vinod's user avatar
  • 21
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 ...
atlasofcoffee's user avatar
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: - ...
Arthur Silva's user avatar
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-...
lpm5152's user avatar
  • 21
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 ...
Jayden's user avatar
  • 121
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 ...
Ivan's user avatar
  • 212
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; ...
jason m's user avatar
  • 113
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 ...
Max Candocia's user avatar
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 ...
IrfanClemson's user avatar
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/...
bneil's user avatar
  • 2,077
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 ...
user1629333's user avatar
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 ...
Reshad's user avatar
  • 131
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 ...
user avatar
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 ...
Ravi Chilakamarthi's user avatar
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 ...
D-Nice's user avatar
  • 21
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 ...
Flotolk's user avatar
  • 176
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 ...
Iujyrino's user avatar
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 ...
Čamo's user avatar
  • 345
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:...
Ian Ling's user avatar
  • 161
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 ...
cidy.long's user avatar
  • 149
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 ...
Anatoly Alekseev's user avatar
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 ...
Raknaros's user avatar
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 ...
Kabe's user avatar
  • 11
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 ...
El_Merendero's user avatar
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, ...
Philippe's user avatar
  • 125
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 ...
shoe's user avatar
  • 121
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 ...
Doofus's user avatar
  • 111
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 ...
Torge's user avatar
  • 273
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 ...
Peter K.'s user avatar
  • 111
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 ...
Nicholas Hubbard's user avatar
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-...
mcandre's user avatar
  • 3,036
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 ...
gelonida's user avatar
  • 365
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/...
Blaine Lafreniere's user avatar
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 ...
Den Lu's user avatar
  • 11
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/...
user394334's user avatar
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, ...
Nandhini Subburaj's user avatar
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 ...
Kefka's user avatar
  • 1,426

1
2 3 4 5