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)

1
vote
2answers
1k views

postgres stats and opennms

What is the best (or easiest?) to monitor postgres 8.4 stats in OpenNMS 1.76? SNMP? How?
2
votes
2answers
257 views

Prevent Postgresql from logging unique key errors without affecting other error logging

Is there a way to prevent Postgresql from logging ERRORs caused by attempted insertions where a UNIQUE KEY constraint is violated? I could change the server_log_min directive, but this would disable ...
3
votes
1answer
1k views

How to Explain a query in PostgreSQL 8.3 that has bind variables

I need to explain an application generated SQL statement in PostgreSQL 8.3. I captured the SQL statement in the log but it has a lot of bind variables. Is their any way that I can explain this SQL ...
6
votes
7answers
10k views

Reloading PostgreSQL after configuration changes

I've made some changes to pg_hba.conf and I want them to take affect. I've found several places where people say that I can tell PostgreSQL to reload, but there are several different techniques listed,...
12
votes
2answers
37k views

Configuring the pg_hba.conf file to allow access from other servers to the database

I have a postgres database on one server and I need access it from another server. I need to edit the pg_hba.conf file, but have now idea what are the steps to edit this file. Server Centos I need ...
18
votes
2answers
21k views

How can I do a dump of only the table structure in PostgreSQL?

In a similar vein to this question, how would I do a schema-only dump in PostgreSQL?
2
votes
3answers
1k views

Duplicating a postgresql database

How do I make a duplicate copy of a postgresql database? Some (I think) steps would be: stop all queries stop the postgresql daemon copy the database files to a new location make some changes to ...
36
votes
6answers
76k views

GRANT SELECT to all tables in postgresql

Is there a one-liner that grants the SELECT permissions to a new user postgresql? Something that would implement the following pseudo-code: GRANT SELECT ON TABLE * TO my_new_user;
1
vote
2answers
2k views

Crate a new, read-only user in postgres

I would like to create a new user in an existing postgresql database on an Ubuntu machine. I want to grant this user a read-only access to all the tables. How do I do it? Do I need to create a new ...
2
votes
3answers
3k views

How does PostgreSQL perform when installed on a VMWare Instance

Does anybody have any experience with how PostgreSQL performs when installed on a VMWare instance compared to real iron? Any noticeable differences? Any recommendations?
16
votes
5answers
16k views

What's the best way to automate backing-up of PostgreSQL databases?

I find it tedious to have to backup databases every week. And I also think weekly backups should be turned into daily backups. If I had to do that, I don't want to do it manually. What's the best way ...
4
votes
1answer
806 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
2k 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 ...
0
votes
1answer
893 views

PostgreSQL copy permissions

I'm using pgadmin to copy a csv file into the database. this worked fine a million times until today. I get the following error Could not open file "/path.csv" for reading: permission denied I ...
20
votes
10answers
12k views

Automatic postgres backup

What is the best way to automatically backup a Postgres database on Linux every day?
2
votes
1answer
1k views

How can tables be hidden from a user in PostgreSQL 8.1?

I have a Windows application that needs to integrate to an existing PostgreSQL 8.1 database. I'm using the OLE driver from http://www.pgoledb.com/. This particular application tries to load the ...
0
votes
1answer
640 views

To fix Postgres' login permissions for a new user

How can you enable the login for heo? I get the following The problem seems to be in permissions for the user/role "heo" and probably in pg_hba.conf. I put the following line to the file ...
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: http://www.revsys.com/...
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?). ...
1
vote
3answers
784 views

Is MySQL Replication on Windows reliable?

We played a bit with postgresql and slony for a master-slave-slave replication setup on windows. But we hit a wall with failover/switchover which is very unreliable. How about MySQL? How reliable is ...
5
votes
3answers
5k views

How do I know if the autovacuumer in postgres 8.3 is actually working?

I have a couple of postgres 8.3 databases, and they are showing symptoms of lack of vacuuming. I've checked the config, and they are in the default state of having the autovacummer turned on. Also,...
2
votes
1answer
248 views

How to make psql completion with Windows?

There is a completion function by the psql command of Cygwin or UNIX There is not the function in psql for a Windows native. How to make psql completion with Windows? may be need readline!?
0
votes
1answer
3k views

posgresql error: /usr/bin/chcon : can't apply partial context to unlabeled file

I'm trying to figure out why my posgresql is failing to start on startup.. I manually run this at the terminal # chkconfig postgresql on # service postgresql start /usr/bin/chcon: can't apply ...
0
votes
3answers
1k views

MySQl, Postgresql replication comparison

One main issue with MySQL replication is that the slave is single threaded. This can cause replication lag. How does Postgresql handle replication? Does it (or add-on such as slony-I) allow multi-...
0
votes
3answers
924 views

How to optimize postgresql for use with Jackrabbit (a lot of blobs)?

Jackrabbit is a implementation of the JCR (Java Content Repository API), and it is used for instance by Artifactory which stores the maven artifacts in it. Therefore most of the stuff consists of ...
0
votes
3answers
8k views

To get PHP communicate with PostgreSQL

My PHP code which I try to use in Firefox <?php // Connecting, selecting database $dbconn = pg_connect("host=localhost dbname=masi user=postgres password=abc") or die('Could not connect'); ...
10
votes
2answers
13k views

How to change in postgresql password of the user using SQL

How to change in postgresql password of the user using SQL. I have user (alex) and his password (e.g. pass) i need to change using sql statement his password to NULL...
2
votes
4answers
4k views

Can't restore PostgreSQL database backup

The backup was created from a database with the UTF-8 encoding using pg_dump. The backup is in the tar format. I then created a new database on another server running the same version of PostgreSQL (...
5
votes
4answers
4k views

Postgresql installation issues on OS X

I used Fink to install postgresql on os x. when i try to start psql, i get: psql: could not connect to server: No such file or directory Is the server running locally and accepting ...
10
votes
4answers
3k views

Fix Control-C in mysql command line programme?

In the mysql command line, pressing Control-C will cancel the programme, and bring you back to bash. In psql, the one for postgres, it will kill the current query and will not stop the psql programme. ...
0
votes
2answers
787 views

PostgreSQL processes and the signals to which they respond

For PostgreSQL 8.3 and later, what are the processes that make up the server and that respond to a query? What signals do the various processes respond to and what effect do the signals have? What ...
1
vote
3answers
3k views

Installing and running MySQL and PostgreSQL-8.4 side by side

Are there any configuration or installation hazards to setup both Postgresql-8.4 and MySQL on the same server and running them side by side, other than performance? The server is running Debian etch. ...
5
votes
3answers
8k 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?
45
votes
4answers
13k views

Postgres equivalent to MySQL's \G?

Does anyone know if Postgres has a way to display query results "prettily", like how MySQL does when ending a query with \G on the command line? For instance, "select * from sometable\G" as opposed ...
2
votes
2answers
5k views

How do I copy a table from one SQL database to another?

I have a database (it's Postgres, if that matters) and I would like to take two of its tables, export them, and then import them into another database. How do I do that? Edited to add Google bait: ...
0
votes
4answers
3k views

How does SELinux affect the /home directory?

I'm migrating a CentOS 5.3 system from MySQL to PostgreSQL. The way our machine is set up is that the biggest disk partition is mounted to /home. This is out of my control and is managed by the ...
3
votes
6answers
887 views

PostgreSQL Tips 'n' Tricks [closed]

This is meant to be a community wiki for collecting together different bits of PostgreSQL knowledge. Nothing is too common or too obscure. Post anything you know that would help others make the most ...
15
votes
2answers
4k views

Postgres: Non zero exit code when executing a sql file?

I am writing a shell script which makes calls to psql using 2 forms... one is by command (-c), the other is by file (-f). e.g. psql -c "create table foo (bar integer)" psql -f foobar.sql One ...
13
votes
3answers
26k views

Postgres: How can I see all SQL statements being executed by the database server?

I'm in the process of reviewing every SQL statement that an application makes against the database, for performance reasons. Is there an easy way to log all statements that are executed by the ...
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. ...
2
votes
1answer
1k views

Request Tracker migrate PostgreSQL to MySQL

I am in the process of migrating a request tracker 3.6 instance from one server to another. The old server uses PostgreSQL and the new one will use MySQL. Is there a way to migrate the data from one ...
1
vote
3answers
1k views

Postgresql behind firewall: Query takes too long

Here is my setup: two CentOS 5.2 boxes on VMWare ESXi 4.0. The first box ip is 192.168.22.52 on eth0 and 192.168.99.1 on eth1. The second box runs PostgreSQL 8.3 with ip 192.168.99.2 on eth0. Here are ...
0
votes
2answers
922 views

What is a good django admin-like tool for postgres?

I am looking for a tool that will give a few people the ability to muck around in our database. Currently I write custom SQL queries every time we need to do something we don't have tools for. I don'...
18
votes
8answers
15k views

What is the best filesystem for insert performance on PostgreSQL?

I'm curious if anyone out there has done any experimentation or comparisons between file systems and database performance. On Linux, I'm wondering what is the optimal file system for a postgres ...
2
votes
2answers
568 views

Recommend books/resources for this stack: linux, lighttpd, postgres, webpy

I'm trying to learn to run a simple server (and python simultaneously, but that's beside the point). The tutorials available at the websites of the aforementioned technologies were enough to get the ...
12
votes
8answers
12k views

Any good PostgreSQL client for linux? [closed]

stackoverflow points me "belongs-on-serverfault" on this, so crossposting. I am frustrated of not having a good Linux GUI administration and development tool for PostgreSQL. pgAdmin III is buggy ...
42
votes
10answers
6k views

PostgreSQL Replication

We constantly bat this around the office, and the question continues to come up. How do you deal with PostgreSQL replication? I'm not even necessarily talking about advanced clusters, just keeping it ...
5
votes
5answers
10k views

how to install fuzzystrmatch.sql pg_trgm.sql on postgresql on mac os x

I have installed postgresql according to this, but I also need fuzzy string match functions, but I have no idea how to install these.
3
votes
2answers
2k views

Postgresql 'invalid page header' recovery

I'm currently working with an embedded system that uses Postgresql for data storage. We currently have a problem where the boxes will sometimes get rebooted with no warning, and no proper shutdown. ...
1
vote
4answers
4k views

How to migrate data from a Firebird database to PostGreSQL on Linux

Are there any good tools to migrate existing firebird databases to PostgreSQL for Linux systems? I've looked at: FBexport which can be used to dump the data as insert statements, but it's mainly ...