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
1answer
687 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
1answer
590 views

Munin Postgres plugins: DBD::Pg not found

I'm trying to activate some of the Postgresql plugins for Munin. When I run munin-node-configure --suggest | grep postgres I get the following output: postgres_bgwriter | no | no [DBD::Pg ...
3
votes
2answers
108 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
4k views

How to ensure a service is running, using Chef?

I am in a situation where Chef might start a service (postgres) but it might subsequently be stopped out-of-band. I want a subsequent Chef run to cause the service to be running. I have tried this: ...
3
votes
1answer
4k views

psql: fe_sendauth: no password supplied

I have an up and running PostgreSQL database. Now I would like to automate some operations on my database but I have a problem with the password. I am using the following bash script: #!/bin/bash ...
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
1answer
508 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
580 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
450 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
204 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
312 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
2k 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
520 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
429 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
104 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
5k 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
570 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
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
774 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
119 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
145 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
923 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
473 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
596 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 ...
3
votes
1answer
526 views

Postgres backup

I have a Bacula script that does an automatic backup of a Postgres Database. The script makes two backups using (pg_dump) of the data base : The schema only and the data only. /usr/bin/pg_dump ...
3
votes
3answers
2k views

Giving a database an alias in postgres

Is there any way to give a postgres database an alias? Essentially, I need a single database to operate with two names, so that I could make queries to, say, DB_ALPHA and DB_ONE and they'd have the ...
3
votes
1answer
485 views

Best way to migrate from MySQL to PostgreSQL

I have to migrate a MySQL database to PostgreSQL. I have tried the mysqldump with --compatible = postresql. I have seen that some poeple use ETL like Talend to make the migration but you must describe ...
3
votes
3answers
1k views

Does Postgres database support differential backups

Does Postgres database support differential backups
3
votes
2answers
3k views

Server Clustering (Django, Apache, Nginx, Postgres)

I have a project deployed with django, Apache, Nginx and Postgres. The project has requirement of live data viewable to customers. The projects main points are: 1. Devices in field send data to ...
3
votes
3answers
5k views

how can i tune postgres to avoid this error?

I'm getting the error ERROR: could not write block 3478284 of temporary file: No space left on device when running the following query: INSERT INTO summary SELECT t1.a, t1.b, SUM(t1.p) AS ...
3
votes
1answer
142 views

PostgreSQL: how do I list the names of the databases?

Simple question. I have access to PostgreSQL. If I knew the name of the database I could psql -d dbname but if I don't know dbname, how can I list what databases are there?
3
votes
1answer
1k views

Postgres replication falling behind - master -> slave, slave, slave

I currently have 1 master server (a) and 3 replication servers (b, c, d) that come directly from the master, the archive_command I use is the following script: https://gist.github.com/Geesu/8640616 ...
3
votes
2answers
488 views

postgresql slave to master table replication

I currently have a few dozen sites pushing data to a central database over VPN with rsyslog. All these records are going into one table (there are roughly 20 - 30 per second being inserted per site). ...