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
3answers
376 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
186 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
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
2answers
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
2answers
401 views

Lock out PostgreSQL users for maintenance

In PostgreSQL 8.4.9 with pgbouncer 1.3.4 in pool_mode = session (but some users connect to the database directly) - when I login as "super user" with psql -U postgres -W postgres What is the ...
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
1answer
694 views

DB Full Vacuum, Cluster, Analyze - in which order?

I have a huge (~150 GBytes) postgresql database whose performance has dropped during the last weeks. In order to improve performance, I intend to perform FULL VACUUM, CLUSTER and ANALYZE. In which ...
3
votes
2answers
111 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

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
2answers
2k views

Can't install libpq-dev, ubuntu 10.10 and postgres 9

I need some headers from the dev-version of postgres 9, which is contained in libpq-dev, for installing the pg gem, but when I execute: sudo apt-get install libpq-dev I get the result: The ...
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
1k 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
1k views

forgot passwords for postgresql users. how do I login?

I forgot passwords for postgresql root user: postgres So I changed pg_hba.conf to have trust method for everything (this is my local dev box) local all postgres ...
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
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
509 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
1answer
2k views

Cannot connect to PostgreSQL unix domain socket

I'm trying to connect to PostgreSQL Unix domain socket from a PHP web application. Relevant system components: CentOS 7 x64 (SELinux enforced) postgresql93 9.3.5-2PGDG.rhel7 nginx 1.6.2-1.el7.ngx ...
3
votes
2answers
623 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
1answer
469 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
1k 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
209 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
316 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
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
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
551 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
431 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
1k 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 ...
3
votes
1answer
2k views

Enable both ident and md5 authentication in PostgreSQL

On Fedora 12, I'm setting up a test database for myself, and pg_hba.conf contains the following by default: # TYPE DATABASE USER CIDR-ADDRESS METHOD # "local" is for Unix domain ...
3
votes
1answer
267 views

PostgreSQL 7.4 => 8.1 migration: timezone issue

Okay, so I'm migrating Project Open to a new server (opensource project management system built on OpenACS which uses AOLServer) and ran into a very strange issue: importing a 7.4 dump automatically ...
3
votes
1answer
1k views

Can I remove create table permission in postgresql 8.3?

Using postgresql 8.3, I've created a read only user like this: create role reportuser with login password 'secret'; and then granted select to all the tables this user needs to access. but I see ...
3
votes
1answer
1k views

Using pg_dump with huge # of tables?

I'm dealing with a database system that can have thousands of tables. The problem is, when I try to back it up using pg_dump, I sometimes get the following error: pg_dump: WARNING: out of shared ...
3
votes
1answer
113 views

Best way to RAID 4 x Intel SSD 910 for PostgreSQL

I have a Linux (debian/ubuntu) server with 4 x Intel SSD 910 800GB pcie that I need to RAID together. The biggest problem with these card is that they give you 4x200GB drives that you cant ...
3
votes
1answer
6k views

Postgres - could not create any TCP/IP sockets

I'm running a rails app in development with postgresql 9.3. When I tried to start passenger server today, I got: PG::ConnectionBad - could not connect to server: Connection refused Is the server ...
3
votes
1answer
656 views

32bit ODBC Postgres driver on Windows 2008 R2 x64

I'm trying to install the Postgres ODBC 32bit driver on a Windows 2008 R2 64bit machine. After installing it, with no errors, I go to the ODBC panel, the 32bit version under the /syswow64 folder and ...
3
votes
2answers
919 views

RHEL SELinux blocking Apache access to PostgreSQL

I'm running a Django app that uses PostgreSQL. The server is running RHEL 6.5 with SELinux. I'm having an issue where the Django app can't connect to the DB, and I think it's because SELinux is ...
3
votes
1answer
2k views

Change and initialize Postgresql 9.3 default data and log directories on Ubuntu 12.04 LTS

On Azure with Ubuntu 12.04 LTS image, we're trying to change Postgres 9.3 default data and log file paths and also initialize them for the first time on a secondary drive /mnt/pgdata. We've starting ...
3
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. ...
3
votes
1answer
812 views

Enable remote postgres access for heroku

I'm setting up my own postgres database to use with a heroku app (I don't wanna pay $200 a month just to have postgis, I don't need a huge database or anything, just postgis). I'm enabling remote ...
3
votes
1answer
123 views

PostrgreSQL privileges granting

Given I am a root user X. I have users A and B. How do I GRANT user A to enable him granting user B to execute a specific set of functions? For example we have user A that owns database_1. User A ...
3
votes
1answer
147 views

What is causing ntoskrnl aka the System process to continue accumulating handles

I have a server which is accumulating handles on the System process aka c:\windows\system32\ntoskrnl.exe. The handles go to 18,000 in one day and keep going forever up at that rate. The server is ...
3
votes
2answers
940 views

Safe initial sync for SQL Server OLEDB/ODBC Replication

I am trying to setup one-way, transactional replication from SQL Server 2008 R2 to PostgreSQL 9.1. I'm trying out the PGNP OLE DB provider. I can successfully create a subscription, and transactions ...
3
votes
1answer
496 views

Oracle DB on drbd or SAN replication with Oracle Data Guard

I'm looking to deploy an application based on Oracle DB, and I was assuming that it would be possible to easily do active/passive clustering (with RH Cluster or Heartbeat) and synchronous replication ...
3
votes
2answers
4k views

Error1053 trying to start postgres as service on windows

I installed postgres on windows7 and tried to start the service through control panel. It produces error 1053. When I try to run postgres.exe from command line, I get this message: C:\Program Files ...
3
votes
2answers
610 views

PostgreSQL tuning recommendation for large server and dataset

I'm working with a PostgreSQL (8.1) server on a dedicated machine with 64 GB of RAM and a fast RAID disk. The data set itself is pretty huge -- we have several tables around 200 GB, more in the 50-100 ...