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)

107
votes
6answers
178k 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 ...
29
votes
3answers
7k 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 ...
27
votes
7answers
32k 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?
23
votes
7answers
26k 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 ...
22
votes
2answers
33k 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 ...
20
votes
3answers
12k 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 ...
20
votes
1answer
244 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
6answers
35k 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;
19
votes
2answers
1k views

Customer wants open postgresql port, how to make that secure?

So, this is the situation. It seems, we need to have an open tcp port 5432 for the world, where a customer has access to his postgresql database. Because obvious reasons, we can't say just "no", only ...
18
votes
3answers
5k 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
10answers
9k views

Automatic postgres backup

What is the best way to automatically backup a Postgres database on Linux every day?
15
votes
4answers
5k 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 ...
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 ...
15
votes
1answer
12k 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
3answers
16k 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
5answers
11k 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 ...
14
votes
7answers
12k 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
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
3answers
5k 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 ...
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 ...
10
votes
5answers
12k 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 ...
9
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. ...
9
votes
3answers
14k 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 ...
9
votes
1answer
7k 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 ...
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
5k 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. ...
8
votes
2answers
7k 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
2answers
7k 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
2k 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
3answers
2k 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
2k 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
2answers
23k 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 ...
6
votes
3answers
6k 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 ...
6
votes
1answer
4k views

PostgreSQL timezone does not match system timezone

I have several PostgreSQL 9.2 installations where the timezone used by PostgreSQL is GMT, despite the entire system being "Europe/Vienna". I double-checked that postgresql.conf does not contain ...
6
votes
4answers
4k 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
3answers
16k views

How to see active connections and “current activity” in PostgreSQL 8.4

I'm investigating an issue with DB connections being left open indefinitely, causing problems on the DB server. How do I see currently open connections to a PostgreSQL server, particularly those using ...
6
votes
2answers
599 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
534 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
4k 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
561 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
2k 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
787 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
2answers
4k views

Can't Login to phpPgAdmin

I'm trying to set up phpPgAdmin on my test machine so that I can interface with PostgreSQL without always having to use the psql CLI. I have PostgreSQL 9.1 installed via the RPM repository, while I ...
6
votes
4answers
856 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
3answers
7k views

Is the community MySQL safe for production use?

Or Will I need to get the enterprise version? This is because I found this on MySQL's site: If you are running a MySQL production level system, we would like to direct your attention to the ...