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
21
votes
5 answers

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 almost constantly. This is for postgres 8.3. My…
Harel
  • 630
  • 2
  • 7
  • 18
21
votes
1 answer

PostgreSQL 9: Does Vacuuming a table on the primary replicate on the mirror?

Running PostgreSQL 9.0.1, with streaming replication keeping one read-only mirror instance up to date. Auto-vaccuum is on on the primary, except for a few tables which are not vacuumed by the auto-vacuum daemon, in an effort to reduce business-hour…
Scott Herbert
  • 586
  • 1
  • 6
  • 13
20
votes
4 answers

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 database to retrieve data. It runs fine for a few…
Cerin
  • 3,600
  • 19
  • 61
  • 79
20
votes
10 answers

Automatic postgres backup

What is the best way to automatically backup a Postgres database on Linux every day?
Liam
  • 1,401
  • 3
  • 19
  • 28
20
votes
4 answers

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 used. I expect some of the queries to kill the…
epochwolf
  • 639
  • 3
  • 11
  • 16
20
votes
7 answers

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 database. Also, what settings (inode, etc) are ideal…
Elijah
  • 537
  • 2
  • 8
  • 17
19
votes
3 answers

Running multiple versions of PostgreSQL on the same Ubuntu server

I have PostgreSQL 8.4 and 9.0 running on the same server (Ubuntu Lucid). I installed them both via apt-get (8.4 with the default package sources, and 9.0 after adding the ppa from https://launchpad.net/~pitti/+archive/postgresql). When I run a…
coffee-grinder
  • 295
  • 1
  • 2
  • 10
18
votes
2 answers

How to specify a client certificate to psql?

I have a Postgres server with a user dev which requires a client certificate to log in. I'm using the command psql "sslmode=require user=dev host=db.prod", which gives me psql: FATAL: connection requires a valid client certificate. I know where the…
Jorn
  • 501
  • 1
  • 4
  • 14
18
votes
2 answers

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 timezone setting, so according to the documentation it…
Martin C.
  • 670
  • 1
  • 6
  • 12
18
votes
1 answer

Can I enable both peer and md5 authentication for the same PostgreSQL local user in pg_hba.conf?

I would like to let a PostgreSQL (super)user be authenticated with either the "peer" or "md5" authentication methods, preferably using local Unix domain sockets in both cases. The problem is that the order of the entries in pg_hba.conf matters, and…
Zilk
  • 351
  • 4
  • 9
18
votes
1 answer

What's the required to make a normal user can create schema on PostgreSQL?

I created a new database test and created user 'eonil' with this command: CREATE ROLE eonil LOGIN ENCRYPTED PASSWORD 'password' NOINHERIT VALID UNTIL 'infinity'; on my PostgreSQL. I run psql -U eonil test. When I tried to make a new schema, it…
Eonil
  • 10,459
  • 16
  • 36
  • 54
18
votes
3 answers

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 etc?
Eonil
  • 10,459
  • 16
  • 36
  • 54
17
votes
7 answers

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 works nicely. But as far as I can see it's only for 2…
mrkafk
  • 379
  • 1
  • 2
  • 7
16
votes
2 answers

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 to add the following line in the file host all …
Elitmiar
  • 775
  • 3
  • 15
  • 31
16
votes
2 answers

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 pgbouncer defaults to default_pool_size=20.…
ChocoDeveloper
  • 422
  • 2
  • 5
  • 11
1 2 3
…
99 100