Server Fault Stack Exchange
Server Fault Stack Exchange

Questions tagged [postgresql]

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 (AIX, BSD, HP-UX, SGI IRIX, Mac OS X, Solaris, Tru64), and Windows.

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 (AIX, BSD, HP-UX, SGI IRIX, Mac OS X, Solaris, Tru64), and Windows. It is ACID compliant, has full support for foreign keys, joins, views, triggers, and stored procedures (in multiple languages). It includes most SQL:2008 data types, including INTEGER, NUMERIC, BOOLEAN, CHAR, VARCHAR, DATE, INTERVAL, and TIMESTAMP. It also supports storage of binary large objects, including pictures, sounds, or video. It has native programming interfaces for C/C++, Java, .Net, Perl, Python, Ruby, Tcl, ODBC, among others, and exceptional documentation.

An enterprise class database, PostgreSQL boasts sophisticated features such as Multi-Version Concurrency Control (MVCC), point in time recovery, tablespaces, asynchronous replication, nested transactions (savepoints), online/hot backups, a sophisticated query planner/optimizer, and write ahead logging for fault tolerance. It supports international character sets, multibyte character encodings, Unicode, and it is locale-aware for sorting, case-sensitivity, and formatting. It is highly scalable both in the sheer quantity of data it can manage and in the number of concurrent users it can accommodate.

PostgreSQL has won praise from its users and industry recognition, including the Linux New Media Award for Best Database System and five time winner of the The Linux Journal Editors' Choice Award for best DBMS.

Best of all, PostgreSQL's source code is available under a liberal open source license: the PostgreSQL License. This license gives you the freedom to use, modify and distribute PostgreSQL in any form you like, open or closed source. Any modifications, enhancements, or changes you make are yours to do with as you please. As such, PostgreSQL is not only a powerful database system capable of running the enterprise, it is a development platform upon which to develop in-house, web, or commercial software products that require a capable RDBMS.

1955 questions
475
votes
5 answers

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?
Thierry Lam
  • 6,261
  • 10
  • 27
  • 24
161
votes
10 answers

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;
Adam Matan
  • 13,194
  • 19
  • 55
  • 75
146
votes
6 answers

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 a specific database? Ideally I'd like to see…
EMP
  • 5,172
  • 10
  • 37
  • 33
135
votes
10 answers

Is there an equivalent of MySQL's SHOW CREATE TABLE in Postgres?

Is there an equivalent of MySQL's 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 (over WCF).
vlebar
  • 1,453
  • 2
  • 9
  • 4
127
votes
4 answers

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 running said command successfully (as the…
rz.
  • 1,373
  • 2
  • 9
  • 7
116
votes
5 answers

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 to "select * from sometable;" Many thanks!
law
  • 1,490
  • 3
  • 11
  • 11
87
votes
9 answers

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 be a valid archive I have done following…
gruszczy
  • 973
  • 1
  • 7
  • 7
66
votes
7 answers

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

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 some interesting benchmarks, but it was a limited…
Russ
  • 713
  • 1
  • 5
  • 7
63
votes
3 answers

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 "yum install libpq". Who can tell me how to install…
larry
  • 4,037
  • 9
  • 36
  • 42
55
votes
1 answer

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 often see people using constructs like: sudo su -…
Craig Ringer
  • 11,083
  • 9
  • 40
  • 61
50
votes
5 answers

unable to connect to public postgresql rds instance

I created a basic test PostgreSQL RDS instance in a VPC that has a single public subnet and that should be available to connect over the public internet. It uses the default security group, which is open for port 5432. When I try to connect, it…
Edward Q. Bridges
  • 1,241
  • 1
  • 9
  • 9
49
votes
6 answers

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?
Steve Bennett
  • 5,750
  • 12
  • 47
  • 59
45
votes
10 answers

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 simple with Master-Slave, Master-MultiSlave, and…
f4nt
  • 859
  • 10
  • 9
44
votes
2 answers

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

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. [fail] the…
Max L.
  • 561
  • 1
  • 4
  • 6
43
votes
3 answers

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 blah_production and import it on development or staging…
Pablo Fernandez
  • 7,438
  • 25
  • 71
  • 83
1
2 3
…
99 100