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,960
questions
478
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,291
163
votes
10
answers
418k
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.4k
146
votes
6
answers
347k
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,172
138
votes
10
answers
167k
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,483
130
votes
4
answers
360k
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,403
118
votes
5
answers
46k
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,510
87
votes
10
answers
191k
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 ...
- 973
66
votes
7
answers
18k
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
98k
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,067
56
votes
1
answer
166k
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.2k
51
votes
5
answers
76k
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,251
50
votes
6
answers
172k
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,830
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
44
votes
5
answers
24k
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,703
44
votes
2
answers
120k
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.
...
- 561
43
votes
3
answers
68k
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,478
40
votes
2
answers
69k
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.6k
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 ...
- 973
34
votes
5
answers
15k
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
33
votes
4
answers
64k
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 ...
- 561
33
votes
4
answers
76k
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
33
votes
6
answers
110k
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 ...
- 901
33
votes
3
answers
74k
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 ...
- 569
32
votes
5
answers
36k
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
4
answers
24k
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
3
answers
42k
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
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 ...
- 3,984
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
28
votes
6
answers
82k
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,185
28
votes
1
answer
64k
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>
- 383
27
votes
5
answers
37k
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,173
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
74k
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,404
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 ...
- 973
25
votes
5
answers
46k
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,010
25
votes
4
answers
136k
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
3
answers
109k
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
25
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, ...
- 361
24
votes
2
answers
31k
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
1
answer
14k
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 ...
- 793
23
votes
6
answers
132k
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
23
votes
6
answers
91k
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
81k
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,540
22
votes
1
answer
360
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,451
21
votes
5
answers
38k
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 ...
- 650
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,401
20
votes
7
answers
22k
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