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.
1,975
questions
481
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,321
167
votes
10
answers
453k
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;
- 13.7k
146
votes
6
answers
352k
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,194
141
votes
10
answers
180k
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,513
136
votes
4
answers
399k
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,463
119
votes
5
answers
49k
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,520
89
votes
10
answers
202k
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 ...
- 993
66
votes
7
answers
19k
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
64
votes
3
answers
101k
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,117
58
votes
1
answer
174k
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 ...
- 11.3k
52
votes
5
answers
83k
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 ...
- 1,271
51
votes
6
answers
179k
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?
- 5,930
45
votes
5
answers
26k
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,893
45
votes
2
answers
125k
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.
...
- 571
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
3
answers
69k
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,518
40
votes
2
answers
71k
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?
- 18.9k
37
votes
2
answers
16k
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 ...
- 983
36
votes
4
answers
82k
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!
user55029
35
votes
4
answers
70k
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 ...
- 581
34
votes
5
answers
16k
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 ...
user239237
34
votes
6
answers
118k
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 ...
- 911
34
votes
3
answers
79k
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 ...
- 589
32
votes
5
answers
38k
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 ...
- 669
30
votes
3
answers
25k
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 ...
- 4,053
30
votes
4
answers
25k
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 ...
user55029
30
votes
4
answers
44k
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,135
29
votes
6
answers
86k
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
- 1,215
29
votes
2
answers
40k
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 ...
- 393
29
votes
1
answer
72k
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>
- 393
27
votes
5
answers
50k
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 ...
- 7,030
27
votes
5
answers
38k
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,203
27
votes
3
answers
6k
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...
Alexander
26
votes
11
answers
78k
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,544
26
votes
3
answers
76k
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 ...
- 983
26
votes
1
answer
9k
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, ...
- 371
25
votes
4
answers
141k
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/...
- 253
25
votes
2
answers
15k
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 ...
- 833
25
votes
3
answers
117k
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
2
answers
38k
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 ...
- 353
24
votes
6
answers
137k
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?
- 401
23
votes
6
answers
95k
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: ...
- 383
22
votes
3
answers
85k
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/...
- 2,600
22
votes
5
answers
40k
views
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 ...
- 660
22
votes
1
answer
366
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,601
21
votes
2
answers
52k
views
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 ...
- 541
21
votes
1
answer
12k
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-...
- 596
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,421
20
votes
7
answers
23k
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 ...
- 547