Questions tagged [postgresql]
PostgreSQL is a powerful, open source object-relational database system. It has more than 15 years of active development and a proven architecture that has earned it a strong reputation for reliability, data integrity, and correctness. It runs on all major operating systems, including Linux, UNIX (AIX, BSD, HP-UX, SGI IRIX, Mac OS X, Solaris, Tru64), and Windows.
459
questions with no upvoted or accepted answers
9
votes
1
answer
924
views
Using ZFS head node as database server?
I'm using a dual-head ZFS-backed NAS for high availability cluster shared storage, based on Nexenta's recommended architecture as seen here:
The disks in 1 JBOD will store the database files for a ...
- 389
7
votes
1
answer
4k
views
postgres not starting after reboot on ubuntu
I'm not familiar with ubuntu (raring), so could someone help me please:
I'm using postgresql 9.1 from apt. It installed the appropriate init script in /etc/init.d/postgresql. I can start the server ...
- 170
6
votes
1
answer
18k
views
Tons of "invalid command \N" when restoring a dump
I'm following these instructions to upgrade postgres from 9.1 to 9.3:
http://www.postgresql.org/docs/9.1/static/upgrading.html
When I restore (Step 8), I get a ton of messages like this:
psql:/mnt/...
- 1,311
6
votes
2
answers
2k
views
Virtual users in sshd from a postgres database
I have a Postgresql database full of user accounts, and i would like to allow these user to access a server through ssh, using only public keys authentification.
So far, i have setup these parts on ...
5
votes
0
answers
2k
views
`systemctl disable --now postgresql` doesn't stop the daemon
I'm running Debian Stretch and PostgreSQL 10 from apt.postgresql.org. When I do:
postgresql disable --now postgresql
the database server doesn't stop. It says:
Synchronizing state of postgresql....
- 2,298
5
votes
0
answers
444
views
Bugzilla extremely slow on EC2, RDS PostgreSQL
The Problem
I have installed Bugzilla on an EC2 instance, using PostgreSQL RDS as my database. After entering a few dozen bugs, Bugzilla became unbelievable slow, and I get a lot of timeouts.
Server ...
- 13.6k
4
votes
1
answer
7k
views
Connect to postgres database behind nginx reverse-proxy using docker containers
I have set up a postgres database container and can connect and query it directly using its published port 5434.
The goal is to deploy the test database later and access it via nginx reverse proxy.
I ...
- 41
4
votes
0
answers
3k
views
Postgresql fails to start with no such file or directory error?
I was juggling Postgresql versions and I think I shot myself in the foot during the renaming of something. I have 9.3 and 9.4 installed on an Ubuntu 14.04 station, and I wanted to go back to 9.3 so I ...
- 161
4
votes
0
answers
5k
views
Postgresql 9.4 clusters not auto starting on Debian
I have Debian 8.2 x64 and Postgresql 9.4 (from repo). I've recreated default cluster in different location like this:
# service postgresql stop
# pg_dropcluster 9.4 main
# pg_createcluster -d /srv/...
- 186
4
votes
2
answers
2k
views
PostgreSQL limiting connections to fewer than max_connections?
I have a PostgreSQL 9.4 server that seems to be limiting connections to fewer than what I have defined in max_connections.
I have max_connections set to 300.
SHOW max_connections;
max_connections
--...
- 233
4
votes
0
answers
1k
views
UDP Overflow / UDP Drops on Standby Postgres Service
I am stumped trying to prevent an overflowing UDP buffer on a standby Postgres service. Any help would be most appreciated.
Essentially a UDP buffer associated with the pg_standby process on my ...
- 41
4
votes
1
answer
2k
views
Administer postgres from PGAdmin on remote mac using ssh tunnel
I've got PostgreSQL installed on a Ubuntu server and I'm trying to connect to that server using PGAdmin on a remote macbook.
I've created an ssh tunnel -
macbook:~postgres$ ssh -L 5423:localhost:...
- 301
4
votes
1
answer
5k
views
Unable to connect to posgres using pgadmin
I have a postgres instance inside a kubernetes cluster. I have setup a port forward and if I use the postgres CLI I can connect and perform queries as expected.
If I connect via pgadmin it appears to ...
- 141
3
votes
0
answers
2k
views
Docker compose permission denied when PHP-FPM trying to create directory
I have this docker-compose I'm experimenting with. Im using fastCGI from Nginx to run the script.
The docker-compose.yml:
version: "3"
services:
psqldb:
build:
context: ....
- 41
3
votes
1
answer
4k
views
PosgreSQL upgrade from 9.6 to 11.6 is failing with source cluster not shutdown properly
While trying to upgrade from 9.6 to 11.6 it is failing with this below error:
su - postgres -c '/usr/pgsql-11/bin/pg_upgrade -d /opt/collabnet/teamforge/var/pgsql/9.6/data -D /opt/collabnet/teamforge/...
- 31
3
votes
0
answers
4k
views
Load testing throws Server 502 error: Bad Gateway after 700 users. Gunicorn, Gevent, Nginx, Django
I'm trying to reach 2000 concurrent users with my benchmarking tool. I'm using locust to simulate them.
My server has 24vCPUs, 128GB RAM, 25SSD.
I want to be able to serve 2000 concurrent users ...
- 163
3
votes
0
answers
1k
views
Sync Local Postgres Database with Remote Database
My company is looking at creating a consolidated server that will serve as a backup for many remote servers who have separate data from one another, and provide an application where the aggregate data ...
- 31
3
votes
0
answers
647
views
Docker Swarm - database connection reset by peer
I am running a Spring Boot application with Docker Swarm and I use Postgres for database.
When I run both of them as Docker service, database connection fails consistently and randomly (as you can ...
- 183
3
votes
0
answers
2k
views
Can't connect to remote host via ssh tunnel on port 5432
I have PostgreSQL installed on a remote host running Debian 8. I must use a ssh tunnel via a proxy server to connect to this remote. I have no problems:
in connecting via ssh on port 22,
in running ...
- 131
3
votes
0
answers
8k
views
postgresql invalid command \
When trying to import an .out file back into a postgres database, I get many (thousands) of rows like this: psql:G:/Backup/main_db_backup.out:251875: invalid command \
With a different number where "...
- 31
3
votes
1
answer
5k
views
Slot replication not working in Postgres 9.4
I am using Postgres 9.4 on Windows 2008 server. There are three such instances that I am using. One as master and the rest of the two are slave/standby. The version of Postgres is 9.4 in all the three ...
- 163
3
votes
1
answer
2k
views
Postgres Restore Not Restoring Binary Data
This is a very urgent situation.
We have a postgres 9.4 database installed on a CentOs machine.
We are trying to take a backup from a Heroku Postgres database and restore it to the database on ...
- 141
3
votes
0
answers
1k
views
security group not allowing connection to postgres ec2 aws
Hey im having some trouble allow remote connections to postgres from other ec2 instances using the named security group feature to rules.
I have set the listens directive in postgres.conf to "*" and ...
- 201
3
votes
1
answer
2k
views
Error in osm data data import while nominatim installation
I am doing nominatim installation on my server. I run the following command
./utils/setup.php --osm-file /usr/share/osm2pgsql/planetfile/planet-latest.osm.pbf --all
to import the pbf file and Few ...
- 31
2
votes
0
answers
73
views
Bacula Postgresql Huge Logs table
We currently have Bacula Enterprise v12 installed on our production.
We seem to have a problem with BWEB regarding the logs of any job. it takes forever for the web interface to load the logs.
After ...
- 21
2
votes
1
answer
332
views
PostgreSQL huge input lookup error could not parse XML document
I have a PostgreSQL database with some pretty big documents in XML format added into a table which has an XML type field. I am running an XMLTABLE query with Xpath expression and I have the following ...
- 71
2
votes
0
answers
501
views
access to a postgres database gets permission denied if startet over systemctl
I have an issue with a postgres database running on my CentOS Server, but only if i start it over systemctl.
My Service config:
Description=Nakama server
[Service]
ExecStart=/etc/nakama/nakama --...
- 21
2
votes
1
answer
2k
views
If I have multiple versions of Postgres installed, how do I use a version specific tool?
For development reasons I have both Postgres 12 and 14 installed on my local machine. However, often I also need to use the pg_dump utility to create backups.
pg_dump 12 won't connect to a Postgres 14 ...
2
votes
0
answers
921
views
"self signed certificate in certificate chain", Server or Client?
I'm creating a SSL communication between a pg DB Server and a node client. After following some docs and implemented with openssl, node complaints "self signed certificate in certificate chain&...
- 121
2
votes
1
answer
3k
views
patroni.exceptions.PatroniFatalException: 'Failed to bootstrap cluster'
I have a problem with patroni right here
[postgres@localhost ~]$ patroni /etc/patroni.yml
2021-05-25 11:39:56,767 INFO: Selected new etcd server http://10.102.196.138:2379
2021-05-25 11:39:56,772 INFO:...
- 51
2
votes
0
answers
168
views
Disk is full after a PostgreSQL operation. Now what?
I added a column to a huge table in PostgreSQL. Since the table occupied more than 50% of the free disk space and the operation was running in a transaction, all of the disk space got consumed by the ...
- 409
2
votes
1
answer
4k
views
PostgreSQL Service status Active(Exited) but I am able to connect
I have installed PostgreSQL (9.5) on a new Ubuntu (16.04) VM on GCD. THe status of service is active(exited). The output of sudo systemctl status postgresql is:
● postgresql.service - PostgreSQL ...
- 21
2
votes
1
answer
6k
views
Postgresql Service not Starting
I'm trying to start a PostgreSQL 10.10 server on an Armbian (Ubuntu Bionic with Armbian Linux 4.14.133-odroidxu4) device (Odroid Xu4). The server was previously working, but after a power failure, it ...
- 21
2
votes
2
answers
903
views
Postgres 10.1 slow on Ubuntu 16.04
Installing a single table-dump is significant slower on U16.04 (and higher) than on U14.04. The test was done on the same computer with default settings.
testdb=# \d photos_searchlog
...
- 439
2
votes
0
answers
298
views
postgres 9.4 hangs when starting as service during centos 6.9 boot
postgres 9.4 hangs when starting as service during centos 6.9 boot.
The server was running well for months, and then we perform a restart and it hangs on starting postgres 9.4 as a service. The disks ...
- 121
2
votes
3
answers
1k
views
Can't install PostgreSQL 9 server on Centos 6
Can't install PostgreSQL server on Centos Machine.
I simply added official repo with PostgreSQL
rpm -ivh https://download.postgresql.org/pub/repos/yum/9.5/redhat/rhel-6-x86_64/pgdg-centos95-9.5-3....
2
votes
0
answers
2k
views
Postgres-Repmgr node is marked as inactive after reboot
I'm using using a cluster with 2 psql servers. I'm using repmgr to manage replication and failover.
Replication works fine, and when my master fail, the standby takes the lead. My problem is when I ...
- 143
2
votes
0
answers
4k
views
RHEL 7 psql: FATAL: Ident authentication failed
I am trying to start the PostgreSQL database, but it's always failing.
Command I am trying:
psql -h localhost -U postgres resulting error:
psql: FATAL: Ident authentication failed for user "...
- 121
2
votes
0
answers
2k
views
pg_restore changing object owner not working ,
I just try the solution found here : https://stackoverflow.com/questions/31469008/postgresql-backup-database-and-restore-on-different-owner
And I still have an error on the inexistent object owner.
...
- 161
2
votes
0
answers
955
views
< hostname[private/dovecot-lmtp]: 550 5.1.1 <[email protected]> User doesn't exist: [email protected]
This is the setup of my mailserver:
Big picture: MTA: Postfix 2.11.3 - MDA: Dovecot 2.2.13 - DB: Postgres 9.4.10 - OS: Debian 8 Jessie, kernel 3.16.0-4
postconf -n
alias_database = hash:/etc/aliases
...
- 21
2
votes
1
answer
848
views
tcp/ip Connection Errors
We have a web-server with 128GB main memory, 32 vCPUs on Ubuntu 16.04. It runs nginx, php-fpm, postgresql, mysql and beanstalkd applications.
The web-server gives some connection errors from ...
- 21
2
votes
0
answers
512
views
PgPool2 alternative for load balancing in Postgresql cluster
I'm using PgBouncer for connection pooling, native psql replication in master-slave mode and PgPool2 for load balancing.
After a few (many) attempts to get PgPool2 playing nice I give up.
To many ...
- 121
2
votes
0
answers
2k
views
Running an init.d startup script as a non-root user (PostgreSQL-related)
I am currently installing PostgreSQL from source on a RHEL6 system. I have generally been following the official documentation for guidance (http://www.postgresql.org/docs/9.4/static/installation....
- 21
2
votes
0
answers
848
views
postgresql in lxc container doesn't start after reboot
Debian stable (Jessie)
I have few containers - in one container there is postgresql. I want to start it first (I hope that my config does it).
After reboot all containers starts, but i can't log in ...
- 793
2
votes
0
answers
725
views
How to automate failover of master in PostgreSQL 9.5 on Windows?
I have 4 nodes (version - PostgreSQL 9.5 Beta) in a slot replication set up. Recently the master node, due to less disk space available, PostgreSQL started throwing errors and crashed in the end. As I ...
- 163
2
votes
0
answers
405
views
Show or log long queries with values instead placeholders
I have any very long sql query in postgresql. I tried show what postgress do with:
select * from pg_stat_activity
unfortunately I see something like:
select some_fields from anytable1 anytable2 ...
- 348
2
votes
0
answers
863
views
Postgres streaming replication over Internet
all:
I want to set up two PostgreSQL servers, one as master server and the other one as a hot standy, using streaming replication. I have done this before in LAN environments, but I would like to ...
2
votes
0
answers
515
views
PHPPGAdmin on Windows - authenticate users against Active Directory
I installed PHP, Apache 2.4, and PostgreSQL 9.4.1 on Windows machine which belongs to Windows domain.
My pg_hba.conf:
hostnossl all postgres 127.0.0.1/32 md5
hostnossl all all ...
- 256
2
votes
0
answers
831
views
How to change username on RDS instance
I have a single RDS postgresql instance with username 'psql' owning the database. How do you change the username?
- 398
2
votes
0
answers
943
views
Upgraded Postgresql from 9.1 to 9.4 on Ubuntu and now extensions are looking in the wrong folder
When I run
CREATE EXTENSION "uuid-ossp";
I get
ERROR: could not open extension control file "/usr/share/postgresql/9.1/extension/uuid-ossp.control": No such file or directory
The extensions ...
- 247