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)

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
802 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
889 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
11k 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
639 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
246 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
922 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
775 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?
44
votes
4answers
12k 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
885 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 ...
14
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
25k 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
914 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
567 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 ...
5
votes
4answers
8k views

Can you explain exporting and then importing ProgreSQL using phpPgAdmin? (And what's with permissions?)

Bounty Edit: I've left the original question but would like a good answer on the whole permissions debacle that Postgres seems to bath in. 'Importing & exporting clients db's: A field guide' I ...
11
votes
7answers
10k views

What Good PostgreSQL Administration Tools Exist? [closed]

Does PostgreSQL have good performance or administration tools? I have been using postgres for quite some time now, but I still do not know a huge amount about ways to optimise and maintain a database,...
1
vote
3answers
1k views

Is Access connecting to a remote Postgresql database via ODBC usable?

I currently have an MS Access application that connects to a PostgreSQL database via ODBC. This successfully runs on a LAN with 20 users (each running their own version of Access). Now I am thinking ...
16
votes
4answers
7k views

Which database server scales better: PostgreSQL or MySQL?

I'm working on a website that could eventually have a very large database. The website will be an art community not until deviantART. I'll be running a lot of queries for analyzing how the site is ...
4
votes
3answers
2k views

PostgreSQL encrypted backups

Is it possible to ensure that dumps taken from a PostgreSQL db are always encrypted? The data in the database is highly sensitive and we cannot afford un-authorized personnel, including Sys Admins ...