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

bzip2 too slow. Multiple cores are avaible

I am running this command: pg_dumpall | bzip2 > cluster-$(date --iso).sql.bz2 It takes too long. I look at the processes with top. The bzip2 process takes about 95% and postgres 5% of one core. The wa entry is low. This means the disk is not the…
guettli
  • 3,591
  • 17
  • 72
  • 123
39
votes
2 answers

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?
warren
  • 18,369
  • 23
  • 84
  • 135
36
votes
2 answers

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 difference between these forms is that a call by command…
Jin Kim
  • 963
  • 2
  • 9
  • 12
34
votes
5 answers

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 last-last resort. What are the biggest troubles?…
user239237
32
votes
3 answers

Why can't I create a superuser in AWS Postgresql instance?

I have an AWS EC2 instance connecting to an RDS instance (Postgresql). When I created the RDS instance, I told it the DB root's username was: my_user1 and the password was password1. Now I'm attempting to create a role and a super-user. But it…
Saqib Ali
  • 559
  • 1
  • 9
  • 18
32
votes
4 answers

How to dump a part of a table from PostgreSQL?

I have a PostgreSQL database with a multi-Gb table (which contains a log of certain events). I need to pass the latest events to an analyst - let's say he only needs events from the last month. How can I produce a dump of only those rows that have,…
Leonid Shevtsov
  • 551
  • 1
  • 5
  • 8
32
votes
5 answers

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 9.0, which is not compatible with this version…
Daniel Fischer
  • 669
  • 1
  • 8
  • 11
32
votes
4 answers

Stop PostgreSQL from starting on Windows?

I have a local postgresql database for development purposes that I dont want to start up every time Windows does - how do I stop it from starting!
user55029
32
votes
6 answers

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 individual databases via SSH. In particular the…
Stephen
  • 891
  • 1
  • 8
  • 8
30
votes
4 answers

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 Linux box instead as stability and performance are…
user55029
29
votes
2 answers

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 types, so I decided to upgrade. I upgraded to 9.3 by…
BitPusher16
  • 393
  • 1
  • 3
  • 4
29
votes
3 answers

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 and ran kill -9 pid. This killed the process…
Banjer
  • 3,974
  • 12
  • 41
  • 47
29
votes
3 answers

How can I show the content of a trigger with psql?

I know I can list the triggers with \dft. But how can I see one concrete trigger? I want to know details like on which events the trigger is executed, which function is executed and so on.
Mnementh
  • 1,125
  • 2
  • 11
  • 18
28
votes
6 answers

Determining PostgreSQL's port

I know that by default PostgreSQL listens on port 5432, but what is the command to actually determine PostgreSQL's port? Configuration: Ubuntu 9.10 with PostgreSQL 8.4
Matthew Rankin
  • 1,175
  • 5
  • 15
  • 32
27
votes
3 answers

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 risks. I have a production Postgres 9.3 server…
vastlysuperiorman
  • 596
  • 6
  • 17
1
2
3
…
99 100