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)

3
votes
4answers
7k views

PostgreSQL service initdb doesn't work

I installed PostgreSQL 9.1.2 on my fedora 16 in a method similar to mentioned here, and tested it, which means that my installation is working fine. But when I do: service postgresql initdb or even ...
3
votes
1answer
484 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
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
2answers
4k 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
9k 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
3answers
6k views

What do I do when pg_cancel_backend doesn't work?

If I have a long-running Postgres query, and regular "kill [pid]" doesn't work, and pg_cancel_backend doesn't work, what should I do?
3
votes
1answer
4k views

Granting rights on postgresql database to another user

I'm trying to set up a system with a PostgreSQL database per user, with a PHP-FPM resource pool for an associated account. I need to grant all privileges on the database to the other user, but it ...
3
votes
3answers
2k views

How can I check if my DB needs more RAM?

How would you check if your postgresql DB instance needs more RAM memory to handle their current working data?
3
votes
3answers
601 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
1answer
2k views

Stop PostgreSQL from starting on Windows?

I have a local postgresql database for development purposes that I dont want to start up every time Windows does - how do I stop it from starting!
3
votes
3answers
185 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
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
2k 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
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
1answer
1k views

PostgreSQL EC2 Backup Strategy

I'm trying to determine the best approach to backing up a PostgreSQL database on Amazon EC2. I've read about a couple of options. 1) Take a daily snapshot of the EBS volume your database is using. ...
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
3k 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
384 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
192 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
1answer
10k 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 ...
3
votes
1answer
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
3k 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 ...
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
1k 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
2answers
118 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
3answers
2k views

PG pool failover leads to two independent masters with network failure

I've got two servers set up with PG Pool to create a HA setup for a webapp. PGPool and postgres run on both servers, using streaming replication from server 1 to server 2. The webapp on each machine ...
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 ...
3
votes
4answers
4k views

PostgreSQL hangs during shut down in linux?

I m using PostgreSQL9.0. It hangs during shutdown. when I used ---pg_ctl stop it hangs and after sometime its displaying msg--- pg_ctl: server does not shut down and when I used psql its ...
3
votes
1answer
4k views

About checkpoint_segments parameter

I don't know the parameter "checkpoint_segments" very well, My personal views is that it means the number of wal log files; I configured the value of the parameter to 64, but I can see 131 files in ...
3
votes
3answers
3k 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 ...
3
votes
2answers
1k views

PostgresQL on Amazon EBS volume, realistic performance, or move to something more lightweight?

I'm working on a little research project, currently running as an instance on ec2, and I'm hoping to figure out whether I'm going down the right path. We, like a thousand other people, are making use ...
3
votes
1answer
512 views

Current replication techniques in PostgreSQL

I'm planning to migrate from MySQL to PostgreSQL, but worried about the apparent luck of replications solutions there. Are there any current replication techniques that can be used out of box? ...
3
votes
2answers
774 views

pg_archivecleanup not found Postgres 9.1, even though it is installed?

I have streaming replication setup on 2 Postgres 9.1 servers. Everything is ok apart from the archivecleanup. In the logs it says: sh: 1: pg_archivecleanup: not found 2013-11-19 09:17:37 GMT WARNING: ...
3
votes
2answers
2k views

Upgrading Postgres.app 9.1 to 9.2, getting lc_collate values do not match error

I'm upgrading a Postgres.app on my Macbook Pro (my development machine) and I'm getting an error when I run: pg_upgrade -b /Users/foo/Library/PostgreSQL/9.1/bin/ -B ...
3
votes
1answer
858 views

Should a connection pooler be run on my database server or my app server?

I'm getting ready to start using PGBouncer, but I'm unsure of whether it should be used on my database server or on the app servers. If it's on the app servers, there will necessarily be multiple ...
3
votes
1answer
524 views

“No version information available” - After installing Postgres

I have installed Postgres 9.1.4 on an Ubuntu 12.04 (precise) 64-bit from here http://www.openscg.com/se/postgresql/packages.jsp, but right after installing many commands (programs) are throwing these ...
3
votes
1answer
2k views

syslog writing to general instead of specific file

I'm running Ubuntu and Postgresql 9. I've enabled postgresql to log to syslog, and added the following directive to the config file: syslog_facility = 'local0'". I've tried to configure syslog ...
3
votes
2answers
225 views

Is it possible to share a PostgreSQL database between Windows and Linux?

Is it possible to use a PostrgeSQL database in Windows and use the same one in Linux after rebooting? Will pointing the data folder to a common folder be sufficient? I know I could test it and then ...
3
votes
1answer
3k views

Postgresql table drop taking very long time

I am kind of new to Postgresql and server administration so I am not quite sure if this is normal behavior or not. Right now I am trying to drop a table with the cascade option. The command I used ...
3
votes
1answer
337 views

How precise is cron?

Hey everyone. I am trying to set up a python script to query a DB for all updates over the past 5 minutes. Given the number of updates, it must be as precise as possible (our records are timestamped ...
3
votes
1answer
3k 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 ...
3
votes
1answer
660 views

Granting user privilege to create/drop database of specific name

I have a PostgreSQL user named mydb_user who has been granted ownership of mydb_dev. mydb_user already has the privilege to drop the database, but I want him to be able to create the database again ...
3
votes
1answer
434 views

Install PostgreSQL on Ubuntu without starting it

I want to install PostgreSQL 8.4 (or any service) on Ubuntu 10.4 (or any Linux distro) without it starting up as part of the installation. Right now when I do a sudo apt-get install postgresql-8.4 ...
3
votes
2answers
2k views

Can't get PostgreSQL to run in Debian Lenny

I'm trying to install PostgreSQL 8.3 on Debian Lenny (kernel 2.6.26-2-686) using the package manager: sudo apt-get install postgresql-8.3 postgresql-client-8.3 postgresql-contrib-8.3 ...
3
votes
2answers
2k views

weird postgresql log entries

I am trying to figure out why I get some weird entries in my postgresql log after I do a restart: 2010-05-14 11:30:25 EEST LOG: database system was shut down at 2010-05-14 11:30:22 EEST 2010-05-14 ...
3
votes
2answers
3k views

PostgreSQL deleteing old records from log tables

I have a PostgreSQL database which stores my RADIUS connection information. What I want to do is only store a month worth of logs. How would I craft an SQL statement that I can run from cron that ...