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
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
2k 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
1answer
3k 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
505 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
527 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
426 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
197 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
2k 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
302 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
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
483 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
2k 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
76 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
455 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
735 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
118 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
143 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
898 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
447 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
3k 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
578 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
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
498 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
483 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
973 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
4k 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
137 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
447 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). ...
3
votes
1answer
1k 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 ...
3
votes
1answer
2k views

Strange high CPU I/O wait on postgresql server

I'm really struggling with a performance issue currently, where all my database queries takes up to a 1000 times more than normally, when my database server is under "heavy" load. I have a server ...
3
votes
1answer
1k views

pg_dump backup compression

When executing a pg_dump which should I use and why? pg_dump -U <user> <database> | gzip -c > backup.gz or pg_dump -F c -f backup.tar.gz -U <user> <database>
3
votes
2answers
3k 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 ...
3
votes
1answer
1k views

How to install PostgreSQL to run under a different system user than “postgres”?

I'm installing PostgreSQL on RedHat Linux using rpm installers and I'm trying to get the application to be owned and run by a system user other than "postgres", for example "dbuser5". It seems I ...
3
votes
2answers
87 views

Does copying a database in PostgreSQL preserve statistics?

When using CREATE DATABASE TEMPLATE in PostgreSQL, will the target database preserve the statistics from the source database?
3
votes
2answers
553 views

Installing Nominatim Pre-Indexed Data

I am trying to install nominatim using this guide. After one day of processing, I finaly get this message: feature 895(k) Killed After a read in dmesg I got: [143978.925793] Out of memory: kill ...