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)

4
votes
1answer
483 views

What grants are required to run pg_dump?

I'm trying to automate the creation of a user for database backup on Postgresql. What grants are required to run pg_dump?
4
votes
1answer
4k views

PostgreSQL has no service name on CentOS

I installed PostgreSQL in a pretty standard way on CentOS 5.5: rpm -ivh http://yum.pgrpms.org/reporpms/9.0/pgdg-centos-9.0-2.noarch.rpm yum install postgresql90-server postgresql90-contrib chkconfig ...
4
votes
7answers
2k views

Linux freezes every few seconds

We're having an issue where one our Linux boxes (Ubuntu 10.04 LTS, running on EC2 with a quadruple-large size, 68GB of RAM and 8 virtual cores with 3.25GHz each) freezes up every few seconds. Typing ...
4
votes
1answer
1k views

Django: CONN_MAX_AGE persists connections, but doesn't reuse them with PostgreSQL

I've got a django setup are using Django 1.6.7 and Postgres 9.3 on Ubuntu 14.04 LTS. At any given time, the site gets about ~250 simultaneous connections to the PostgreSQL database, which is a Quad ...
4
votes
2answers
429 views

Why is domU faster than dom0 on IO?

I have installed debian 7 on a physical machine. This is the configuration of the machine: 3 hard drives using RAID 5 Strip element size: 1M Read policy: Adaptive read ahead Write policy: Write ...
4
votes
1answer
6k views

Maintenance of tables: do I need to REINDEX a table after truncating and repopulating?

I have a table with about 2 million rows in it of transactional data that we use for analytics. Every week we reload this with new data, so we've been using TRUNCATE to clear it out and then inserting ...
4
votes
1answer
1k views

How to import a dump in Postgres from a newer version?

We have a postgres-databse under windows, that is version 9.1. From that we want to dump the data and import under a Linux-server, that runs Postgres 8.4. The data include spatial data with Postgis. ...
4
votes
4answers
4k views

How could I import Postgres data dumps into MS SQL?

I have some data that is from a Postgres database dump (not csv or anything) and I am looking to get it into MS SQL. Is there an easy way to do this or a free tool that doesn't have limits on data ...
4
votes
1answer
2k views

How to configure rsyslog to log into PostgreSQL without too much latency?

I'm trying to configure RSyslog on a Debian machine to log everythingo into PostgreSQL, while also logging as usual on disk. I'm using a pretty stock Debian configuration and I activated the related ...
4
votes
3answers
5k 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 ...
4
votes
1answer
686 views

Option shared_buffers in PostgreSQL

I have a server with 4GB of RAM and PostgreSQL 9.0.3 on Centos 5. I'm using pgbench and pgbench-tools to measure performance using two pgbench-tools queries: select and tpc-b. With default settings ...
4
votes
1answer
703 views

How to build docbook 4.2 docs on Arch Linux?

I'm trying to build documentation for PostgreSQL HEAD. It requires docbook 4.2, which I installed, but still the problem exist. I described it in detail - including error messages, lists of packages, ...
4
votes
2answers
186 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 ...
4
votes
0answers
223 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. ...
4
votes
0answers
637 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 ...
3
votes
8answers
1k views

RAID and Databases

I use Postgresql 8.4 and I wanted to know what type of RAID is mostly used for databases. I read everywhere that RAID10 is the best suited and RAID5 not a good option. ex: ...
3
votes
3answers
333 views

Choosing between Debian and Ubuntu

Debian or Ubuntu for online multiplayer game cluster? (latest versions both) I plan to run: Erlang apps (ejabberd + modules) with many-many connection Java apps for business logic Memcached with ...
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. ...
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 ...
3
votes
3answers
6k views

How to check throughput (query per second) of a database?

I have PostgreSQL dbms. I want to check live throughput of the DBMS. I want to build a graph of throughput which will be updated every 10 seconds. How do I do that? Any suggestion for MySQL will ...
3
votes
1answer
772 views

Tuning Postgresql on a 16GB Linode VPS

I'm running a dedicated PostgreSQL on a 16GB Linode (Ubuntu 12.04 32bit). I've tried using pgtune, but I'll run into shared memory errors. I.e. The PostgreSQL server failed to start. Please check the ...
3
votes
3answers
338 views

If I `sudo reboot` my Ubuntu 12.04.2 server with Postgres running, can I lose data?

I've been wondering for the longest time if a sudo reboot with a running Postgres (namely, 9.2.4) server can (or definitely will) lead to data loss or other problems (e.g., inability to start again ...
3
votes
1answer
518 views

Lost PostgreSQL Superuser Username and Password

I have a PostgreSQL 8.4 database running on Windows, but I've lost the name of the superuser username, so am unable to connect. I've already configured pg_hba.conf to use trust to allow connections ...
3
votes
2answers
5k views

Multiple instances of Postgres after Ubuntu upgrade

Just upgraded Ubuntu to latest version but now I have two instance of Postgres. 8.4 and 9.1 The problem is that I do not know on what port the second one is running ? How can I detect on what port ...
3
votes
5answers
11k views

How to Best Manage High PostgreSQL CPU Use?

I am trying to fix a high PostgreSQL CPU use problem. We are using PostgreSQL 8.0.9 and when our JEE web application (in JBoss) is used in certain load increase conditions, top shows a slow increase ...
3
votes
2answers
198 views

Postgresql Internals - Documentation

I'm looking for some up-to-date information about postgresql internals, specifically the query optimizer. I've found this link (referred to in the "Further Reading" section of the 8.4 docs):- ...
3
votes
2answers
2k views

Postgresql read replication using an amazon postgresql RDS service as master

we currently are using amazon Postgresql 9.3 RDS as a master database for a gaming platform. We would like to create a read only replication to use for reporting and heavy queries. Yes, it seems ...
3
votes
2answers
7k 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 ...
3
votes
3answers
696 views

Getting more database performance - postgresql

There is a django "web application" (from hell) we are hosting on a dedicated server. After some benchmarking I found that the "Application" is amazingly bad at database operations (refactoring is not ...
3
votes
3answers
186 views

Suggestions for spatial database systems [closed]

I'm looking for suggestions on a database for large sets of spatial master data. The data itself is not relational, but could be formulated as such. The data as-is is structed as: latitude, ...
3
votes
2answers
2k views

How do MySQL logs compare to Postgresql logs?

MySQL has quite a few logs: InnoDB transaction log Binary log General query log Error log Slow query log I know about Postgresql's WAL, which is equivalent to InnoDB's transaction log (correct?). ...
3
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 ...
3
votes
2answers
4k views

Why doesn't sudo know where psql is?

The problem The CKAN installation documentation shows you how to list the installed PostgreSQL databases. The command looks like this: sudo -u postgres psql -l When I try that in my shell, I get ...
3
votes
2answers
6k views

Postgres INSERT ERROR: permission denied for schema public

In Postgres I created the following table inside a db called testing: CREATE TABLE category_google_taxonomy ( category_id integer references category ON UPDATE CASCADE ON DELETE CASCADE, ...
3
votes
2answers
3k views

Complete solution to upgrade PostgreSQL on debian production server

I'm using Debian 6 (Squeeze) in production for a couple of websites. I decided to use postgresql backports so that I could use PostgreSQL 9.0 features. I thought that it would remain 9.0 and receive ...
3
votes
3answers
3k views

PostgreSQL 9.0.X or 8.4.X - performance, replication, stability

I've been developing an app w/ server infrastructure for a while now, and I'm getting ready to release soon(ish). So far the server backend has been running on PostgreSQL 8.4.7, and it's working as ...
3
votes
3answers
4k views

PostgreSQL won't start because it “Cannot allocate memory”

I'm running PostgreSQL 8.4.5 on Ubuntu 10.04. I run an EC2 instance with a few sites running on nginx. Most of these sites run on Django and connect to this Postgres instance. For some reason, at ...
3
votes
3answers
397 views

php postgres night mares

A lecturer demanded that we do some homework in PHP and PostgreSQL, so I downloaded a package off the internet called "The BitNami WAPPStack" which had php, phpPgAdmin. The problem is I have failed ...
3
votes
2answers
2k views

Why can't user login on Postgres

I have attempted to set up a read-only user in Postgres, but they are unable to log on. I am unsure the next step to take to solve the problem. When I try and log on as user XXXX I get an error - ...
3
votes
1answer
8k views

pg_dump: [archiver (db)] connection to database failed: FATAL: Peer authentication failed for user “postgres”

I'm trying to backup my Postgres DB to another server but I keep getting denied access. my pg_hba.conf file looks like this: # DO NOT DISABLE! # If you change this first entry you will need to make ...
3
votes
1answer
1k views

pg_hba.conf entry for host

Im getting this error with postgre in my centos vps Unable to connect to PostgreSQL server: FATAL: no pg_hba.conf entry for host "::1", user "xxx", database "xxx_xxx", SSL off in Here is my ...
3
votes
3answers
4k views

How to convert a postgres database to sqlite

We're working on a website, and when we develop locally (one of us from Windows), we use sqlite3, but on the server (linux) we use postgres. We'd like to be able to import the production database into ...
3
votes
4answers
2k views

Install postgresql on RHEL 5.6

When I try to run yum install postgresql I get the following error message: Transaction Check Error: file /usr/lib64/libecpg.so.6.1 from install of postgresql-libs-8.4.4-2PGDG.el5.x86_64 ...
3
votes
2answers
2k views

Postgres application tries to use incorrect socket file

I have a local postgres server running (on ubuntu linux). It is listening via a socket file: $ ls -la /var/run/postgresql/ total 8 drwxrwsrwx 2 postgres postgres 100 2011-04-15 19:06 . drwxr-xr-x ...
3
votes
1answer
1k views

How to count unique values?

I am trying get the number of unique ip_addresses (in this case '3'). The table looks like this: Structure: CREATE TABLE bandits ( key text NOT NULL, ip_address inet, offence text, count ...
3
votes
1answer
1k 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 ...
3
votes
1answer
8k views

Connect to RDS from EC2 instance — both inside VPC

I have a VPC with two private subnets (two were required to get an RDS instance set up). I have a security group for this VPC, let's call it vpc-sg. vpc-sg allows TCP access on port 22 from my home ...
3
votes
2answers
128 views

Use database for sudo

I asked a similar question today, about how to authenticate my server users to a postgres database instead of the /etc/passwd file or LDAP, and I got some useful answers there. I figured out that I ...
3
votes
1answer
3k views

what does peer authentication mean for postgresql?

I'm trying to log into my DB and am getting this error... root@---:~# psql -U user database psql: FATAL: Peer authentication failed for user "user" What does this mean? I'm pretty sure I can fix ...
3
votes
1answer
2k views

Multiples authentication methods for Postgresql

I presently use "md5" authentication for access from the lan: host all all 192.168.1.0/24 md5 I want to add ldap authentication method, so I added this line before: host ...