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 ...

learn more… | top users | synonyms (1)

42
votes
10answers
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 ...
36
votes
6answers
72k 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;
2
votes
2answers
225 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?
1
vote
3answers
783 views

Slow PostgreSQL response over VPN

I'm having a problem with a medical records program that uses a Postgre SQL as a backend. Several offices are connecting to a corporate site via Cisco VPNs. There is an ASA5055 at corporate and most ...
0
votes
1answer
739 views

Database connection to postgresql refused for flask app under mod_wsgi when started by systemd

I'm having a weird problem where a flask application I am trying to run (https://github.com/pyfarm/pyfarm-master) under mod_wsgi under Apache under CentOS 7 cannot connect to a local postgresql server ...
211
votes
6answers
376k 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?
41
votes
7answers
46k 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 ...
29
votes
8answers
45k views

How to restart postgres on OS X?

After a reboot, postgres on my machine hasn't restarted. What command should I use in Terminal to get it going again?
20
votes
3answers
2k views

how to secure an open PostgreSQL port

So, this is the situation. It seems we need to have an open TCP port 5432 to the world, where a customer has access to his PostgreSQL database. For obvious reasons, we can't say just "no", only as a ...
3
votes
2answers
4k 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 a ...
18
votes
4answers
8k 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 ...
10
votes
3answers
3k 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 ...
8
votes
1answer
19k 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 ...
5
votes
1answer
2k 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 ...
4
votes
4answers
11k 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 ...
3
votes
1answer
6k views

Recover PostgreSQL database from filesystem backup

I have a PostgreSQL 8.3 data directory backup. I need to copy a database from this backup into a new PostgreSQL instance. Due to problems with the old server I cannot do a pg_dump of the database. ...
10
votes
1answer
8k 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. ...
6
votes
6answers
14k 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
4
votes
3answers
13k views

Setting up Postgres: Can't connect remotely to Postgres server

I am having a problem getting postgres to accept connections from my other server. Here's my setup: APP06 (10.55.129.31): Server running Postgres APP05 (10.55.129.30): Server trying to connect to ...
3
votes
1answer
8k 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).
3
votes
3answers
1k views

Does Postgres database support differential backups

Does Postgres database support differential backups
2
votes
1answer
5k 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 ...
1
vote
2answers
3k 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 ...
1
vote
1answer
3k 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 ...
7
votes
2answers
3k 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 ...
4
votes
1answer
6k 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 ...
2
votes
3answers
6k 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 ...
1
vote
1answer
603 views

Can't create clusters in PostgreSQL 9.0

I'm trying to setup and run multiple clusters of PostgreSQL 9.0 in the same Ubuntu/Debian machine. Debian has a package called 'postgresql-common' to facilitate managing database clusters. However, ...
0
votes
1answer
640 views

could not write temporary statistics file “pg_stat_tmp/pgstat.tmp”: No space left on device

The posgtres sudden stopped on the server and the logs shows the following error: 2015-08-18 02:29:22 IST ERROR: could not extend file "base/197893/198337": No space left on device 2015-08-18 ...
0
votes
4answers
2k views

Reset postgresql

I want to bring postgresql back to its original empty state. On Ubuntu: $ sudo service postgresql stop $ sudo apt-get purge postgresql* Followed by $ sudo apt-get install postgresql Does what ...
10
votes
1answer
5k 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 ...
4
votes
2answers
713 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 ...
3
votes
2answers
8k views

How to restore a file system level copy of a PostgreSQL database (not dump) to a different PC

I am new to PostgreSQL. I have to recover a database which was running in widows XP machine. I have the zip folder of postgres. I have extracted postgres installation in a different PC and started a ...
2
votes
1answer
432 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.
2
votes
2answers
903 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 ...
2
votes
1answer
7k views

How to install PostgreSQL extension for PDO in Linux?

I have the VPS. I can build my PHP version using Easy Apache. I can find the option for PDO support for MySQL, SQLite. But I don't have any option of PDO pgsql driver. How can I enable that? It says ...
1
vote
3answers
12k 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 ...
1
vote
1answer
248 views

Invalid byte sequence for character encoding 0x00 after upgrading from 8.3 to 8.4

I'm trying to upgrade a database from PostgreSql 8.3 to 8.4, I backed up the data from 8.3 and restored from 8.4 without issues, but, when I try to use the same program that is working perfectly ...
1
vote
1answer
3k views

Why can't I connect to my PostgreSQL database?

I'm trying to install tentd-admin. The install fails during this step: $ DATABASE_URL=postgres://localhost/tent_server bundle exec rake db:migrate I also tried this: $ ...
1
vote
1answer
282 views

Backup broken PostgreSQL 8.4 without pg_dump

So. I have a problem. PostgreSQL 8.4 won't start or restart without any output given. But it worked for 3 monthes until hosting provider doesn't rebooted server. Now it is completly broken. It wan't ...
1
vote
1answer
2k views

Empty error on starting almost-default PostgreSQL

When trying to start PostgreSQL, I get this 'empty' error message: root@mountain-lion:/home/ubuntu# service postgresql start * Starting PostgreSQL 9.1 database server * Error: could not exec ...
1
vote
1answer
2k views

Trying to determine the correct number of XFS allocation groups for postgresql server on Linux

I am running a postgres 8.4.5 server on the linux 2.6.33.7 kernel on an 8 disk raid array with an LSI controller. Most of the tables are around 1GB or less. I know that XFS uses allocation groups ...
1
vote
2answers
2k 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 ...
0
votes
1answer
1k 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 ...
0
votes
1answer
589 views

Why is autovacuum using so much IO when running on a partition's master table after upgrading PosgreSQL from 8.4 to 9.1?

We have an application where we are collecting timestamp-based data. Since a month of data goes over 30M rows (and growing each month), we decided to partition the data on an year-and-month basis. We ...
0
votes
1answer
880 views

rpm -Uvh & yum install

I am new to linux and wants to understand the reason for using some commands. I am trying to install PostgreSQL on CentOs by following the instructions here. First we do rpm -ivh ...
0
votes
1answer
3k views

Strange permission errors in new PostgreSQL installation

A freshly installed PostgreSQL (with configuration overwritten) won't start: $ sudo service postgresql start * Starting PostgreSQL 9.1 database server * Error: could not read ...
0
votes
1answer
873 views

Connect to and reindex Postgres database with system table errors

I've got a Postgres database that crashed (as well as the disk) and suffered some data errors (per http://serverfault.com/questions/156152/after-db-disk-failure-now-cache-lookup-failed-for-index). ...
0
votes
1answer
1k views

After DB disk failure, now “cache lookup failed for index”

After resolving some problems with the network drive on which our Postgres database was hosted, we encountered this error: OperationalError: FATAL: cache lookup failed for index 2662 That ...
0
votes
2answers
4k views

postgres on 64 bit linux or 64 bit windows server 2008?

Postgres does not have a 64 bit binary for windows server quote "As there is generally no reason to run with shared_buffers > 256 - 512MB on Windows, there isn't a great deal of incentive to put in ...