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.

Filter by
Sorted by
Tagged with
469 votes
5 answers
1.2m 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?
  • 6,141
153 votes
10 answers
359k 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;
  • 12.8k
146 votes
6 answers
339k 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 ...
  • 5,152
128 votes
10 answers
144k views

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 ...
  • 1,383
114 votes
3 answers
298k 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 ...
  • 1,243
109 votes
5 answers
43k 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 ...
  • 1,420
84 votes
9 answers
175k 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 ...
  • 943
66 votes
7 answers
17k 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 ...
  • 713
63 votes
3 answers
91k 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 "...
  • 4,007
52 votes
1 answer
151k 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 ...
  • 10.7k
50 votes
5 answers
62k views

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 ...
47 votes
6 answers
156k views

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?
45 votes
10 answers
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 ...
  • 859
43 votes
2 answers
114k views

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. ...
  • 551
43 votes
3 answers
67k 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 ...
  • 7,319
39 votes
5 answers
21k views

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. ...
  • 3,281
37 votes
2 answers
64k 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?
  • 18k
36 votes
2 answers
15k 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 ...
  • 963
34 votes
5 answers
14k 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 ...
user avatar
32 votes
5 answers
33k 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 ...
32 votes
3 answers
64k views

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 ...
  • 549
31 votes
4 answers
68k views

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!
user avatar
31 votes
6 answers
95k 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 ...
  • 841
30 votes
4 answers
56k views

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 ...
29 votes
4 answers
22k 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 ...
user avatar
28 votes
3 answers
24k 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 ...
  • 3,924
28 votes
2 answers
39k 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 ...
27 votes
5 answers
36k 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 ...
  • 1,143
27 votes
3 answers
5k 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 ...
27 votes
2 answers
31k 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...
user avatar
26 votes
6 answers
76k views

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
26 votes
3 answers
39k views

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.
  • 1,075
26 votes
3 answers
74k 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 ...
  • 963
25 votes
3 answers
94k views

psql: fe_sendauth: no password supplied

I have an up and running PostgreSQL database. Now I would like to automate some operations on my database but I have a problem with the password. I am using the following bash script: #!/bin/bash ...
  • 427
24 votes
11 answers
67k views

Reloading PostgreSQL after configuration changes

I've made some changes to pg_hba.conf and I want them to take affect. I've found several places where people say that I can tell PostgreSQL to reload, but there are several different techniques listed,...
  • 1,234
24 votes
2 answers
20k views

PgAdmin: could not send data to server: Socket is not connected could not send SSL negotiation packet: Socket is not connected [closed]

While opening query tool via pgadmin, i am getting this error on popup. could not send data to server: Socket is not connected could not send SSL negotiation packet: Socket is not connected Does ...
24 votes
1 answer
13k views

Django: CONN_MAX_AGE persists connections, but doesn't reuse them with PostgreSQL

I've got a django setup are using Django 1.6.7 and Postgres 9.3 on Ubuntu 14.04 LTS. At any given time, the site gets about ~250 simultaneous connections to the PostgreSQL database, which is a Quad ...
  • 783
24 votes
1 answer
52k views

pg_dump backup compression

When executing a pg_dump which should I use and why? pg_dump -U <user> <database> | gzip -c > backup.gz or pg_dump -F c -f backup.tar.gz -U <user> <database>
  • 343
24 votes
1 answer
8k 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, ...
23 votes
6 answers
122k views

Location of postgresql.conf and pg_hba.conf on an Ubuntu server

Where are the files postgresql.conf and pg_hba.conf on a Linux server running PostgreSQL 8.4 installed from Ubuntu repos?
  • 391
22 votes
4 answers
126k views

postgresql installation : initdb data directory not empty?

I am trying to create the postgresql database. When I install PostgreSQL, I gave this command: sudo yum install postgresql postgresql-server and later I modified the config file: sudo vim /var/lib/...
  • 223
22 votes
3 answers
73k views

Completely reset PostgreSQL to default?

Question Is there a way to completely reset a PostgreSQL installation on Linux, so it is in the same state as when I installed it? Idea I have considered rm -rf /var/lib/pgsql/* rm -rf /var/lib/...
22 votes
1 answer
341 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 ...
  • 1,251
21 votes
5 answers
40k views

What's a convenient way to execute multiline queries in postgres using ssh?

Say I need hand write some queries in the console, what's the most efficient way of executing multiline queries like CREATE TABLE statements? I am used to using Microsoft Management Studio, but I now ...
21 votes
6 answers
83k views

Ubuntu get PostGreSQL running

I have installed PostGreSQL 8.3 on an Ubuntu machine and I want to configure a pssw for it, however when I run this command: sudu su postgres -c psql template1 I get the following error: "psql: ...
21 votes
1 answer
11k views

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-...
20 votes
10 answers
15k views

Automatic postgres backup

What is the best way to automatically backup a Postgres database on Linux every day?
  • 1,353
20 votes
7 answers
21k 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 ...
  • 537
20 votes
4 answers
10k 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 ...
  • 639
20 votes
4 answers
40k 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 ...
  • 3,560

1
2 3 4 5
39