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)

220
votes
6answers
399k 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?
59
votes
7answers
8k 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 ...
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 ...
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 ...
41
votes
7answers
48k 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 ...
37
votes
2answers
67k 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 ...
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;
29
votes
3answers
31k 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 "...
29
votes
8answers
46k 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?
28
votes
3answers
21k 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 (...
23
votes
3answers
1k views

Risk of starting NTP on database server?

I've heard rumors of bad things happening to database and mail servers if you change the system time while they are running. However, I'm having a hard time finding any concrete information on actual ...
22
votes
4answers
8k 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 ...
21
votes
3answers
32k views

Export and import a PostgreSQL database with a different name?

Is there a way to export a PostgreSQL database and later import it with another name? I'm using PostgreSQL with Rails and I often export the data from production, where the database is called ...
21
votes
1answer
278 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 ...
20
votes
10answers
12k views

Automatic postgres backup

What is the best way to automatically backup a Postgres database on Linux every day?
20
votes
3answers
3k views

how to secure an open PostgreSQL port

So, this is the situation. It seems we need to have an open TCP port 5432 to the world, where a customer has access to his PostgreSQL database. For obvious reasons, we can't say just "no", only as a ...
19
votes
3answers
47k 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 ...
19
votes
4answers
8k 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 ...
18
votes
3answers
13k 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 ...
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 ...
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?
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 ...
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 ...
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 ...
15
votes
2answers
8k 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 ...
15
votes
1answer
1k views

Postgres roles best practice implementation

Folks, I could use your help making my Postgres user access control design better and more aligned with best practices. I'm helping roll out a small production Postgres server but I am not a DB admin,...
14
votes
7answers
12k 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 ...
13
votes
5answers
20k 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 ...
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 ...
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 ...
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 ...
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,...
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. ...
10
votes
1answer
27k views

postgres server fails to start, produces no log, how to to troubleshoot? [closed]

I'm starting up a postgres 9.3 instance on a ubuntu 12.04 server : ~# service postgresql start * The PostgreSQL server failed to start. Please check the log output. ...
10
votes
1answer
16k 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 ...
10
votes
3answers
12k 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 ...
10
votes
1answer
12k views

PostgreSQL pg_hba.conf problem adding a host?

I'm having a problem with configuring my postgresql server. I've installed PostgreSQL 9.0.2 on slackware 13.1x64 and it runs fine but I can't add a IP that could connect to PostgreSQL remotely. The ...
10
votes
5answers
16k views

How to restore PostgreSQL database from .tar file?

I have all PostgreSQL databases backed up during incremental backups using WHM, which creates a $dbName.tar file. Data is stored in these .tar files, but I do not know how to restore it back into the ...
10
votes
1answer
8k 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. I'...
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...
10
votes
3answers
3k 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
6k 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
1answer
10k 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 ...
9
votes
2answers
18k views

PostgreSQL ERROR: no more connections allowed

How do you free up PostgreSQL connections that aren't properly being closed by client applications? I have a data-mining app that spins up multi processes, all connection to a local PostgreSQL 9.1 ...
8
votes
2answers
10k views

I have multiple PostgreSQL servers installed. How do I disable auto boot for older server?

I'm running Ubuntu 12.04 on Oracle VirtualBox. A couple months ago, I installed PostgreSQL server version 9.1 on my machine. Just recently, I learned that PostgreSQL server 9.3 supports JSON data ...
8
votes
1answer
21k views

What's the difference between sudo su - postgres and sudo -u postgres?

PostgreSQL users peer authentication on unix sockets by default, where the unix user must be the same as the PostgreSQL user. So people frequently use su or sudo to become the postgres superuser. I ...
8
votes
3answers
5k 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 ...
8
votes
7answers
3k 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
8k views

How to ensure a service is running, using Chef?

I am in a situation where Chef might start a service (postgres) but it might subsequently be stopped out-of-band. I want a subsequent Chef run to cause the service to be running. I have tried this: ...
8
votes
2answers
10k views

How to calculate max_connections for PostgreSQL and default_pool_size for pgbouncer?

Is there a rule or something I can use to calculate a good number for max_connections, default_pool_size and max_client_conn? The defaults are odd. PostgreSQL defaults to max_connections=100 while ...