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)

89
votes
7answers
144k views

What's the default superuser username/password for postgres after a new install?

I have just installed postgres 8.4 on Ubuntu 9.10 and it has never asked me to create a superuser. Is there a default superuser and its password? If not, how do I create a new one?
54
votes
7answers
5k views

What are the drawbacks of running a database inside a virtual machine? How do I overcome them? [closed]

Running anything inside a virtual machine will have some level of performance hit, but how much does it really impact the performance of a database system? I found this academic reference paper with ...
40
votes
10answers
5k 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 ...
25
votes
7answers
30k views

How to restart postgres on OS X?

After a reboot, postgres on my machine hasn't restarted. What command should I use in Terminal to get it going again?
22
votes
3answers
6k 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 ...
20
votes
1answer
241 views

Is it possible that just one bit switches so my file shows me a letter “Q” instead of a “S”

In our application we use Hibernate and PostgreSQL to store data. In one of our database tables we have a discriminator column which says for example "TIPPSPIEL". It is a fixed string and can not be ...
19
votes
7answers
23k views

pg_dump and pg_restore: input file does not appear to be a valid archive

I have used pg_dump on one machine and copied result file to another, where I tried to restore it. I believe schema is the same. However, I get: pg_restore: [archiver] input file does not appear to ...
17
votes
9answers
9k views

Automatic postgres backup

What is the best way to automatically backup a Postgres database on Linux every day?
17
votes
6answers
29k 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;
17
votes
3answers
4k views

How do I upgrade postgresl database? Incompatibility error

I installed postgresql via Homebrew. I have the following issue after upgrading: FATAL: database files are incompatible with server DETAIL: The data directory was initialized by PostgreSQL version ...
17
votes
3answers
11k views

Is there a MySQL equivalent of SHOW CREATE TABLE in postgres?

Is there a MySQL equivalent of SHOW CREATE TABLE in postgres? Is this possible? If not what is the next best solution? I need the statement because I use it to create the table on an remote server ...
15
votes
1answer
28k views

Postgresql: what does GRANT ALL PRIVILEGES ON DATABASE do?

I'm trying to grant all privileges on all tables of a given database to a new postgres user (not the owner). It seems that GRANT ALL PRIVILEGES ON DATABASE my_db TO new_user; does not do that. After ...
15
votes
4answers
5k 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 ...
14
votes
3answers
14k views

How to install libpq-dev on Centos 5.5

In order to develop one web application based on postgresql, i need to install libpq on my centos. I can install it by "apt-get install libpq-dev" on ubuntu, but i can not install it on centos by ...
14
votes
1answer
11k 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?
14
votes
7answers
11k 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 ...
13
votes
5answers
10k 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 ...
12
votes
4answers
4k views

Is PostgreSQL suited to one OS? Is it better on Linux than Windows?

I have been running PostgreSQL on Windows Server 2003 without a hitch and its fast, so to answer my own question it seems fine. However I am about to launch a new project and am considering using a ...
12
votes
2answers
6k views

Where is initdb in Ubuntu

I want to reset my postgresql database back to its initial condition after install. Apparently initdb is the way to do this, but although the command has a man page, it doesn't seem to be anywhere on ...
12
votes
8answers
9k 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 ...
12
votes
7answers
9k views

USABLE multi-master replication for Postgres?

I tried Postgres-XC and it doesn't implement full SQL yet (like SERIAL) Postgres-R looks interesting but it's "not production ready" according to developers. So I used pgpool-II 3.0.1. Yes, it ...
11
votes
7answers
8k 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 ...
10
votes
3answers
4k views

kill -9 a postgres process

A postgres SELECT query ran out of control on our DB server and started eating up tons of memory and swap until the server ran out of memory. I found the particular process via ps aux | grep postgres ...
10
votes
5answers
11k views

Should I install 32-bit database or 64-bit database?

(I've read a lot about 64-bit versus 32-bit OS/Apps, but this question is specifically in regards to databases.) I'm trying to understand the pros and cons of 32-bit versus 64-bit databases, and ...
10
votes
3answers
2k views

Extremely Slow IO with Simple PostgreSQL 8.4.4 Queries on Centos 5.5

The strange and extremely slow IO pattern I'm seeing is this (output of iostat -dxk 1 /dev/xvdb1): Device: rrqm/s wrqm/s r/s w/s rkB/s wkB/s avgrq-sz avgqu-sz await svctm ...
10
votes
1answer
3k views

How to use a custom status command for a service in puppet?

I am using debian squeeze with PostgreSQL 9.1 from backports. Puppet has version 2.7.14. Unfortunatly the init script returns the wrong exit code for status. Therefore I wrote a custom status command ...
8
votes
4answers
2k 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. ...
8
votes
7answers
2k views

Is MySQL better than PostgreSQL in something?

I know the question sounds provocative, but it really isn't. I'm lately finding MySQL limiting in a lot of areas and liking PostgreSQL more and more. It scales a lot better and it respects the SQL ...
8
votes
1answer
6k views

Do I need to REINDEX and VACUUM a table after deleting lots of rows?

I am running a PostgreSQL database that has several tables which store logging information. This information is for reporting purposes only and gets dumped to a file and deleted from the database if ...
7
votes
3answers
12k 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 ...
7
votes
2answers
6k views

Postgresql Performance - Adjusting SHMMAX and SHMALL

I've read just about everything there is online about improving postgres performance, but the "right" values for SHMMAX and SHMALL still elude me. The consensus seems to be SHMMAX = total_memory/4 ...
7
votes
3answers
4k views

Postgres vs MySQL: Which one is more difficult to scale out?

From your experience, which database serve is more difficult to scale? MySQL or Postgres? I know MySQL has several out-of-the-box scaling/clustering features, but with Postgres there is none that ...
7
votes
2answers
1k 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 ...
7
votes
1answer
4k views

Can't connect to PostgreSQL on VirtualBox guest

When I try to connect from a PostgreSQL client on the host system to the PostgreSQL server on a VirtualBox guest system, I get a "connection attempt failed" message. The host system is Windows XP. ...
7
votes
2answers
6k 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...
7
votes
3answers
1k views

Postmaster uses excessive CPU and Disk Writes

using PostgreSQL 9.1.2 I'm seeing excessive CPU usage and large amounts of writes to disk from postmaster tasks. This happens even while my application is doing almost nothing (10s of inserts per ...
6
votes
4answers
1k views

PostgreSQL scaling up to 64 cores?

In this Computer World article, it specifies that PostgreSQL can scale up to a core limit of 64. Does this mean for one multi-core processor of 64 cores? Or multiple processors with fewer cores? The ...
6
votes
4answers
3k views

Postgres DISK IO very high. What can I do to reduce it immediately?

I'm aware faster disks than what I'm using will help but this will take longer to put in and I am trying to use some emergency measures to decrease disk IO. atop is reporting DSK usage on the red ...
6
votes
2answers
441 views

Why is Postgres sitting 95% idle, with no file I/O?

I have a TileMill/PostGIS stack running on an 8 core Ubuntu 12.04 VM on an OpenStack cloud. It's a rebuild of a very similar system which was running nicely on very similar hardware (same cloud, but ...
6
votes
1answer
419 views

Using Postgres Basic Auth in NGINX to add query result to Proxy HTTP Header

We are trying to use the ngx_postgres module to do basic authentication through NGINX. We want to retrieve a value from the query result-pair and add it to the HTTP Header, and then proxy this to ...
6
votes
4answers
3k views

iotop shows kswapd0 at 99.99% with 0% DISK READ and DISK WRITE

I have a heavy postgres query. It takes over ten minutes to run. I'd like to upgrade my hardware to get it run faster. I think more RAM will help, but my motherboard is full so I'll need a whole ...
6
votes
1answer
506 views

PostgreSQL: Living without root

This is not a question with a concrete answer, I know, but one that seeks experienced views... The OS environment I run is as vanilla as possible so it's often undesirable to install things into root ...
6
votes
1answer
1k views

Bad performance for a large join query in PostgreSQL 8.4.4, despite enough memory to cache entirely

Question How can I make the query described in this post faster, in particular by making PostgreSQL using the available RAM? - Note that I have tried to configure effective_cache_size and ...
6
votes
3answers
776 views

Development, Testing, Staging and Production model

I'm developing a run of the mill django powered website with a Postgres database. I develop locally and have 3 VPS servers for Testing, Staging and Production. Each VPS runs their own Linux / Apache ...
6
votes
4answers
780 views

postgresql on Ubuntu 10.4 doesn't start on boot

I'm trying to have postgresql 8.4 start on boot on Ubuntu server 10.04 (64bit). First, I tried putting: su -c 'pg_ctl start -D /home/postgres -l /home/postgres/serverlog' --preserve-environment ...
5
votes
4answers
6k views

Postgres error message: FATAL: Ident authentication failed for user “…”

Say you're seeing this message: FATAL: Ident authentication failed for user "..." What are the causes of this error message?
5
votes
3answers
5k views

What's difference between “varchar” and “text” type in PostgreSQL?

All I know about differences of them is varchar has limit, and text is not. The documentation does not mention about this. Is that really the only difference? No consideration about performance or ...
5
votes
2answers
20k 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 ...
5
votes
3answers
263 views

Correct way to handle security threats to web server on budget [closed]

During our annual security review I was reminded of an incident earlier this year where we received a threat to our organizations web server. It was over a organization policy and threatened to DDoS ...
5
votes
2answers
684 views

How to scale out OpenStreetMap data efficiently

For over a year now, I'm running an in-house PostGIS server filled with OSM data, used for both Mapnik-based tile generation and Nominatim-based geocoding, updated with day replicates. This works ...