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.

Filter by
Sorted by
Tagged with
52 votes
5 answers
80k views

Unable to connect to public PostgreSQL RDS instance

I created a basic test PostgreSQL RDS instance in a VPC that has a single public subnet and that should be available to connect over the public Internet. It uses the default security group, which is ...
Edward Q. Bridges's user avatar
4 votes
1 answer
1k views

SELinux/PostgresQL "denied { open } for [..] comm="pg_ctl" path="$PGDATA/postgresql.conf"

I've installed PostgresQL on a SELinux-enabled Centos 7 box and changed it's default data directory to /srv/postgres, a separate LUKS-encrypted LVM volume group/logical volume, for reasons of mobility,...
LANerd's user avatar
  • 61
45 votes
10 answers
6k views

PostgreSQL Replication

We constantly bat this around the office, and the question continues to come up. How do you deal with PostgreSQL replication? I'm not even necessarily talking about advanced clusters, just keeping it ...
f4nt's user avatar
  • 859
30 votes
4 answers
24k views

Is PostgreSQL suited to one OS? Is it better on Linux than Windows?

I have been running PostgreSQL on Windows Server 2003 without a hitch and its fast, so to answer my own question it seems fine. However I am about to launch a new project and am considering using a ...
user avatar
29 votes
2 answers
40k views

I have multiple PostgreSQL servers installed. How do I disable auto boot for older server?

I'm running Ubuntu 12.04 on Oracle VirtualBox. A couple months ago, I installed PostgreSQL server version 9.1 on my machine. Just recently, I learned that PostgreSQL server 9.3 supports JSON data ...
BitPusher16's user avatar
19 votes
3 answers
30k views

Running multiple versions of PostgreSQL on the same Ubuntu server

I have PostgreSQL 8.4 and 9.0 running on the same server (Ubuntu Lucid). I installed them both via apt-get (8.4 with the default package sources, and 9.0 after adding the ppa from https://launchpad....
coffee-grinder's user avatar
2 votes
2 answers
370 views

How to install Postgres on LINUX with two verions?

I want to upgrade my postgres8.3 to 8.4 for single domain. Don't affect other domain. How to manage it. Is it possible?
user586445's user avatar
481 votes
5 answers
1.2m views

What's the default superuser username/password for postgres after a new install?

I have just installed postgres 8.4 on Ubuntu 9.10 and it has never asked me to create a superuser. Is there a default superuser and its password? If not, how do I create a new one?
Thierry Lam's user avatar
  • 6,321
166 votes
10 answers
437k views

GRANT SELECT to all tables in postgresql

Is there a one-liner that grants the SELECT permissions to a new user postgresql? Something that would implement the following pseudo-code: GRANT SELECT ON TABLE * TO my_new_user;
Adam Matan's user avatar
  • 13.6k
88 votes
10 answers
197k views

pg_dump and pg_restore: input file does not appear to be a valid archive

I have used pg_dump on one machine and copied result file to another, where I tried to restore it. I believe schema is the same. However, I get: pg_restore: [archiver] input file does not appear to ...
gruszczy's user avatar
  • 983
56 votes
1 answer
171k views

What's the difference between sudo su - postgres and sudo -u postgres?

PostgreSQL users peer authentication on unix sockets by default, where the unix user must be the same as the PostgreSQL user. So people frequently use su or sudo to become the postgres superuser. I ...
Craig Ringer's user avatar
  • 11.2k
50 votes
6 answers
176k views

Postgres error message: FATAL: Ident authentication failed for user "..."

Say you're seeing this message: FATAL: Ident authentication failed for user "..." What are the causes of this error message?
Steve Bennett's user avatar
44 votes
2 answers
123k views

postgres server fails to start, produces no log, how to to troubleshoot?

I'm starting up a postgres 9.3 instance on a ubuntu 12.04 server : ~# service postgresql start * The PostgreSQL server failed to start. Please check the log output. ...
Max L.'s user avatar
  • 561
29 votes
3 answers
25k views

kill -9 a postgres process

A postgres SELECT query ran out of control on our DB server and started eating up tons of memory and swap until the server ran out of memory. I found the particular process via ps aux | grep postgres ...
Banjer's user avatar
  • 4,024
28 votes
6 answers
84k views

Determining PostgreSQL's port

I know that by default PostgreSQL listens on port 5432, but what is the command to actually determine PostgreSQL's port? Configuration: Ubuntu 9.10 with PostgreSQL 8.4
Matthew Rankin's user avatar
22 votes
5 answers
39k views

Postgres DISK IO very high. What can I do to reduce it immediately?

I'm aware faster disks than what I'm using will help but this will take longer to put in and I am trying to use some emergency measures to decrease disk IO. atop is reporting DSK usage on the red ...
Harel's user avatar
  • 660
22 votes
3 answers
83k views

Completely reset PostgreSQL to default?

Question Is there a way to completely reset a PostgreSQL installation on Linux, so it is in the same state as when I installed it? Idea I have considered rm -rf /var/lib/pgsql/* rm -rf /var/lib/...
Jasmine Lognnes's user avatar
17 votes
7 answers
16k views

USABLE multi-master replication for Postgres?

I tried Postgres-XC and it doesn't implement full SQL yet (like SERIAL) Postgres-R looks interesting but it's "not production ready" according to developers. So I used pgpool-II 3.0.1. Yes, it works ...
mrkafk's user avatar
  • 389
14 votes
1 answer
51k views

How do I link a security group to my AWS RDS instance

I have a postgres db setup on RDS. It is running great. However, I want to link this to a set of autoscaled EC2 instances sitting behind an ELB that all reside in a single EC2 security group. I've ...
nu everest's user avatar
14 votes
2 answers
10k views

postgresql log queries single database

I'm using PostgreSQL (8.3) with multiple databases... I'm wondering if there is some way to log the queries made only in one of the databases (not all of them). Or to have one logfile per database......
arod's user avatar
  • 602
14 votes
2 answers
14k views

OOM despite available memory (cache)

We've been running into the OOM killer despite nearly half of our memory being used for the FS cache. We've been logging memory stats once per minute (as reported by top), but there seems to be ...
xyzzyrz's user avatar
  • 1,665
13 votes
2 answers
37k views

PostgreSQL exits after being started

My PostgreSQL 9.5 server on Debian 8 (Jessie) keeps exiting directly after being started via service postgresql start: # service postgresql status ● postgresql.service - PostgreSQL RDBMS Loaded: ...
Florian Brucker's user avatar
12 votes
1 answer
17k views

Can't connect to PostgreSQL on VirtualBox guest

When I try to connect from a PostgreSQL client on the host system to the PostgreSQL server on a VirtualBox guest system, I get a "connection attempt failed" message. The host system is Windows XP. I'...
Don Kirkby's user avatar
  • 1,494
11 votes
3 answers
7k views

Too much I/O generated by postgres stats collector process

I am using XenServer with several virtual machines having local postgres databases. Even when all applications are unused and the databases are idle, each vm causes constant storage network traffic ...
nn4l's user avatar
  • 1,336
11 votes
2 answers
23k views

Crate a new, read-only user in postgres

I would like to create a new user in an existing postgresql database on an Ubuntu machine. I want to grant this user a read-only access to all the tables. How do I do it? Do I need to create a new ...
Adam Matan's user avatar
  • 13.6k
11 votes
2 answers
29k views

I think I have multiple postgresql servers installed, how do I identify and delete the 'extra' ones?

I seem to have a few installations of postgresql on my machine somehow. I'm not sure if this is a mistake or, if Ubuntu for some odd reason duplicates direcotories and keeps them elsewhere. I have ...
Guided33's user avatar
  • 213
10 votes
2 answers
23k views

Postgresql 9.2 "invalid locale name" on Ubuntu 12.04

Today I installed Postgresql 9.2 on Ubuntu precise via apt.postgresql.org – all was relatively painless until I got stuck in a "invalid locale name: it_IT.utf8" error: root@ubuntu:/tmp# sudo -u ...
giorgiga's user avatar
  • 225
10 votes
3 answers
4k views

Extremely Slow IO with Simple PostgreSQL 8.4.4 Queries on Centos 5.5

The strange and extremely slow IO pattern I'm seeing is this (output of iostat -dxk 1 /dev/xvdb1): Device: rrqm/s wrqm/s r/s w/s rkB/s wkB/s avgrq-sz avgqu-sz await svctm %...
ehsanul's user avatar
  • 427
10 votes
1 answer
7k views

How to use a custom status command for a service in puppet?

I am using debian squeeze with PostgreSQL 9.1 from backports. Puppet has version 2.7.14. Unfortunatly the init script returns the wrong exit code for status. Therefore I wrote a custom status command ...
MMore's user avatar
  • 543
7 votes
4 answers
31k views

Configure BIND with database backend and DLZ support

Decided to move my windows based DNS servers to linux. The problem is I need to be able to dynamically update zone files without having to restart bind. It seems the most popular solution is to ...
bwight's user avatar
  • 791
7 votes
4 answers
18k views

Postgres cannot start on lion due to missing server configuration file

I am trying to setup pg on a relatively clean lion install and it's not being successful. First I successfully installed homebrew, and installed postgresql without any errors. Then I tried to run a ...
Andrew Lauer Barinov's user avatar
6 votes
1 answer
7k views

How can I set up an Amazon RDS for PostgreSQL database to act as a replica to a non-RDS PostgreSQL database?

I'm trying to set up an PostgreSQL database on Amazon RDS as a replica to another PostgreSQL database that lives on a regular server. Most of the instructions I've found online require shell access so ...
Dan Loewenherz's user avatar
6 votes
1 answer
38k views

PostgreSQL - Could not extend file No space left on device. HINT: Check free disk space

I've been inserting data into PostgreSQL DB, and got the error message: psycopg2.OperationalError: could not extend file "base/16384/61892": No space left on device HINT: Check free disk space I am ...
Eric Kim's user avatar
  • 173
5 votes
2 answers
7k views

ssl on postgres

I'm trying to set up my postgres server to do ssl connections. I set up the postgresql.conf and the pg_hba.conf correctly. I created a key and self-signed cert (server.crt and server.key) in the ...
Eric Snow's user avatar
  • 155
5 votes
1 answer
18k views

How to restart postgresql 8.4 on Debian Squeeze when there are no clusters defined?

How to restart postgresql 8.4 on Debian Squeeze? /etc/init.d/postgresql stop/start/restart does nothing (no output given).
Daniil Ryzhkov's user avatar
5 votes
2 answers
19k views

Postgres connection establishment slow

If I wan to connect to postgres 9.1.1 db on debian lenny with PgAdmin, it takes >15 seconds until the connection is established. There is nothing to see in the logs why it takes so long. Any ideas ...
markus's user avatar
  • 1,110
5 votes
1 answer
3k views

How do I remove Slony from a restored PostgreSQL database?

I've restored a database which came from a server on which Slony was running. The server on which the database has been restored does not have Slony installed. When the database restored, there were ...
Scott Herbert's user avatar
4 votes
1 answer
17k views

PostgreSQL SSL root.crt not loading

I am running PostgreSQL 9 on Ubuntu (from their PPA repository). I am using OpenSSL 0.9.8o. I have generated keys and certificates using TinyCA2 for both a pg server and the psql client. I ...
malaverdiere's user avatar
4 votes
3 answers
12k views

.\postgres looses "Log on as a service" after reboot; PostgreSQL service does not start

I've installed PostgreSQL 9.1 x64 on a Windows 7 Enterprise x64 system using the usual install method. The computer has a Novell Client for Windows, and a ZENworks Adaptive Agent, which I suppose ...
Mike T's user avatar
  • 751
4 votes
2 answers
2k views

choosing the right RAID level for PostgresQL database

I got an disk array appliance of 8 disks 1T each (UltraStor RS8IP4). It will be used solely by PostgresQL database and I am trying to choose the best RAID level for it. The most priority is for read ...
Sergey's user avatar
  • 41
3 votes
2 answers
19k views

Errors when attempting to connect to PostgreSQL 9.6 using SSL wildcard server certificate and no client certificates

I have a PostgreSQL 9.6.11 database on Amazon Linux that has been configured with a 2048-bit SSL wildcard server certificate and password-based (no client certificates) remote connections since ...
Parker's user avatar
  • 783
3 votes
6 answers
969 views

PostgreSQL Tips 'n' Tricks [closed]

This is meant to be a community wiki for collecting together different bits of PostgreSQL knowledge. Nothing is too common or too obscure. Post anything you know that would help others make the most ...
3 votes
1 answer
4k views

postgresql - Approach for 'live'/hot file backup?

Forgive me for the ignorant question, but I see postgres has their WAL logs, and there are talks about using filesystem snapshots, and the WAL with the snapshots may or may not be sufficient backup/...
dhartford's user avatar
  • 325
2 votes
3 answers
25k views

RoundCube login to local IMAP server failing

I've been following this guide to set up a mail server on my server: http://flurdy.com/docs/postfix/edition5.html The only difference is that this tutorial uses a MySQL database and I've used ...
morgoth84's user avatar
  • 161
2 votes
2 answers
1k views

SELinux prevents Apache from writing to pgbouncer's Unix domain socket

I'm using freshly installed CentOS 5.7 Linux 64 bit with PGDG packages: # rpm -qa|grep -i PG postgresql-libs-8.4.8-2PGDG.rhel5 pgdg-centos-8.4-2 postgresql-8.4.8-2PGDG.rhel5 pgbouncer-1.3.4-1.rhel5 ...
Alexander Farber's user avatar
2 votes
2 answers
3k views

How to upgrade PostgreSQL on EC2?

I have an Amazon EC2 instance running PostgreSQL 9.1. I am pointing to the RedHat repo for PostgreSQL. Now that 9.2 is out, how do I upgrade? This is new to me.
n8gard's user avatar
  • 123
1 vote
0 answers
1k views

Production server much slower then local test server when sending large request

I am running a django app and I am trying to send an API POST-request which is pretty big to my production server. Now the problem is that what takes 4 seconds locally takes 1 minute in production. ...
Micromegas's user avatar
1 vote
1 answer
2k views

Centos Postgres Failed to start

I have CentOs 6.7 installed on my server: bash-4.1# uname -r 2.6.32-573.18.1.el6.x86_64 After server restart, my PostgreSQL 9.6 server is not starting due to locale issue. Here is the log error ...
Anil Bharadia's user avatar
1 vote
1 answer
12k views

PostgreSQL is out of control: high cpu load, high memory consumption [duplicate]

I'm using PostgreSQL 9.2.32, installed on CentOS 7 with Plesk, with a very small amount of tables (the tables are small too) and Vapor 3 as backend. After a few hours, my server is out of memory, ...
Pascal Friedrich's user avatar
1 vote
2 answers
2k views

PostgreSQL not listening on local eth1 after reboot

I have a server with 2 networking interfaces - global on eth0, and local on eth1 with IP like 10.181.xx.xx. Running Ubuntu 16.04. I set up PostgreSQL to listen on this local address, so another ...
Peter Adrianov's user avatar