Skip to main content

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
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?
Thierry Lam's user avatar
  • 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;
Adam Matan's user avatar
  • 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 ...
EMP's user avatar
  • 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 ...
vlebar's user avatar
  • 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 ...
rz.'s user avatar
  • 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 ...
law's user avatar
  • 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 ...
gruszczy's user avatar
  • 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 ...
Russ's user avatar
  • 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 "...
larry's user avatar
  • 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 ...
Craig Ringer's user avatar
  • 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 ...
Edward Q. Bridges's user avatar
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?
Steve Bennett's user avatar
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. ...
guettli's user avatar
  • 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. ...
Max L.'s user avatar
  • 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 ...
f4nt's user avatar
  • 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 ...
Pablo Fernandez's user avatar
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?
warren's user avatar
  • 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 ...
Jin Kim's user avatar
  • 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!
user avatar
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 ...
Leonid Shevtsov's user avatar
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 ...
user avatar
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 ...
Stephen's user avatar
  • 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 ...
Saqib Ali's user avatar
  • 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 ...
Daniel Fischer's user avatar
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 ...
Banjer's user avatar
  • 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 ...
user avatar
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.
Mnementh's user avatar
  • 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
Matthew Rankin's user avatar
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 ...
BitPusher16's user avatar
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>
d4v3y0rk's user avatar
  • 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 ...
Chad Harrison's user avatar
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 ...
Randell's user avatar
  • 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 ...
vastlysuperiorman's user avatar
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
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,...
Don Kirkby's user avatar
  • 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 ...
Jin Kim's user avatar
  • 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, ...
JP Beaudry's user avatar
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/...
sridhar's user avatar
  • 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 ...
synic's user avatar
  • 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 ...
alibaba's user avatar
  • 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 ...
Bhavesh Tilvani's user avatar
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?
Oleksandr's user avatar
  • 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: ...
Tony The Lion's user avatar
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/...
Jasmine Lognnes's user avatar
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 ...
Harel's user avatar
  • 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 ...
Janning's user avatar
  • 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 ...
Jorn's user avatar
  • 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-...
Scott Herbert's user avatar
20 votes
10 answers
15k views

Automatic postgres backup

What is the best way to automatically backup a Postgres database on Linux every day?
Liam's user avatar
  • 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 ...
Elijah's user avatar
  • 547

1
2 3 4 5
40