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
7 votes
4 answers
31k views

Configure BIND with database backend and DLZ support

Decided to move my windows based DNS servers to linux. The problem is I need to be able to dynamically update zone files without having to restart bind. It seems the most popular solution is to ...
bwight's user avatar
  • 791
7 votes
1 answer
27k views

issues configuring firewall rules for Postgres on Centos

I am attempting to follow this guide to allow remote connections to postgres http://www.cyberciti.biz/tips/postgres-allow-remote-access-tcp-connection.html At the moment I am running into issues with ...
Jon Heckman's user avatar
7 votes
1 answer
9k views

How do you stop PostgreSQL from starting a cluster under linux (Ubuntu)

I need to re-build a cluster due to some incorrect parameters set when it was first created. In particular every database in the cluster is set to LC_COLLATE of en_GB.UTF8 and needs to be changed to ...
Philip Couling's user avatar
7 votes
4 answers
18k views

Postgres cannot start on lion due to missing server configuration file

I am trying to setup pg on a relatively clean lion install and it's not being successful. First I successfully installed homebrew, and installed postgresql without any errors. Then I tried to run a ...
Andrew Lauer Barinov's user avatar
7 votes
4 answers
24k views

PostgreSQL hangs during shut down in linux?

I m using PostgreSQL 9.0. It hangs during shutdown. when I used pg_ctl stop it hangs and after sometime its displaying msg: pg_ctl: server does not shut down and when I used psql its showing: psql: ...
Anjali's user avatar
  • 189
7 votes
1 answer
37k views

pg_dump: [archiver (db)] connection to database failed: FATAL: Peer authentication failed for user "postgres"

I'm trying to backup my Postgres DB to another server but I keep getting denied access. my pg_hba.conf file looks like this: # DO NOT DISABLE! # If you change this first entry you will need to make ...
Drace's user avatar
  • 175
7 votes
4 answers
30k views

How can I tune postgres to avoid this error?

I'm getting the error ERROR: could not write block 3478284 of temporary file: No space left on device when running the following query: INSERT INTO summary SELECT t1.a, t1.b, SUM(t1.p) AS p, COUNT(...
pstanton's user avatar
  • 673
7 votes
1 answer
4k views

Ident Authentication with pgAdmin III

Is there a way to get pgadmin to do ident auth for local users? It would be nice to not have to set a password for my local user on a development machine and simply be able to use psql and pgadmin3 ...
Jim Mitchener's user avatar
7 votes
3 answers
6k views

PG pool failover leads to two independent masters with network failure

I've got two servers set up with PG Pool to create a HA setup for a webapp. PGPool and postgres run on both servers, using streaming replication from server 1 to server 2. The webapp on each machine ...
user705142's user avatar
7 votes
1 answer
4k views

Using Postgres Basic Auth in NGINX to add query result to Proxy HTTP Header

We are trying to use the ngx_postgres module to do basic authentication through NGINX. We want to retrieve a value from the query result-pair and add it to the HTTP Header, and then proxy this to ...
user669615's user avatar
7 votes
3 answers
6k views

Multiples authentication methods for Postgresql

I presently use "md5" authentication for access from the lan: host all all 192.168.1.0/24 md5 I want to add ldap authentication method, so I added this line before: host ...
Francis's user avatar
  • 481
7 votes
1 answer
5k views

ZFS Heavy Write Amplification due to Free Space Fragmentation

I have setup ZFS RAID0 for PostgreSQL database. The Storage and Instances are in AWS EC2 and EBS volumes. NAME SIZE ALLOC FREE EXPANDSZ FRAG CAP DEDUP HEALTH ALTROOT pgpool 479G ...
The Coder's user avatar
  • 171
7 votes
1 answer
2k views

Cannot upgrade postgresql 9.4 -> 9.5beta2 after postgis 2.1.6 -> 2.2.0 upgrade

Here is my situation: I started with Postgresql 9.4.5 and Postgis 2.1.6. Everything running smoothly. Both are installed from source. I upgraded Postgis to 2.2.0 (download source, configure, make, ...
elynnaie's user avatar
  • 201
7 votes
1 answer
4k views

How to import a dump in Postgres from a newer version?

We have a postgres-databse under windows, that is version 9.1. From that we want to dump the data and import under a Linux-server, that runs Postgres 8.4. The data include spatial data with Postgis. ...
Mnementh's user avatar
  • 1,135
7 votes
2 answers
14k views

How does one launch PhpPgAdmin?

I just installed postgresql, php5 and in particular PhpPgAdmin using Ubuntu's synaptic app. The PHP is running fine, the http server (lighttpd) is working fine, and I can do pg a the command line. ...
DarenW's user avatar
  • 171
7 votes
1 answer
4k views

Docker: Permission denied on folders added with ADD

I'm trying to configure a postgresql installation using docker. FROM ubuntu:13.10 ADD . /db # install postgres # ... RUN chown postgres:postgres /db -R && \ chmod 700 /db -R USER ...
Carl Levasseur's user avatar
7 votes
1 answer
4k views

postgres not starting after reboot on ubuntu

I'm not familiar with ubuntu (raring), so could someone help me please: I'm using postgresql 9.1 from apt. It installed the appropriate init script in /etc/init.d/postgresql. I can start the server ...
Chris's user avatar
  • 170
6 votes
2 answers
21k views

What is a typical Compression Ratio for an SQL Database backup file? [closed]

One of my clients want us to give a safe estimate of how much disk space does a compressed SQL backup file take. They need to size their server hard disks accordingly. We can rouhgly estimate the ...
user1034912's user avatar
  • 1,364
6 votes
2 answers
9k views

Multiple instances of Postgres after Ubuntu upgrade

Just upgraded Ubuntu to latest version but now I have two instance of Postgres. 8.4 and 9.1 The problem is that I do not know on what port the second one is running ? How can I detect on what port ...
Cris's user avatar
  • 255
6 votes
2 answers
12k views

Nagios command not transmitting all arguments

I'm using the following service to monitor our postgres db from nagios: define service{ use test-service ; Name of servi$ host_name ...
markus's user avatar
  • 1,110
6 votes
1 answer
21k views

Accessing to a remote PostgreSQL server using port forwarding to another machine

I try to access to a Server_A on which PostgreSQL is running. This server is on a local network, that's why I have to forward port from Server_B which is accessible and in the same network. To access ...
Thibaut Guirimand's user avatar
6 votes
1 answer
8k views

Postgresql 9.3 installation fails

I'm trying to install postgresql 9.3 in my ubuntu lucid machine. The problem is that I'm getting this error when running apt-get install postgresql-9.3 => apt-get install postgresql-9.3 Reading ...
Linus Oleander's user avatar
6 votes
2 answers
16k views

Difference of postgreSQL's "trust" and "ident"?

In postgreSQL's /var/lib/pgsql/data/pg_hba.conf trust and ident can be set as method e.g. like so. # TYPE DATABASE USER CIDR-ADDRESS METHOD local all postgres ...
Sandra's user avatar
  • 10.5k
6 votes
1 answer
38k views

PostgreSQL - Could not extend file No space left on device. HINT: Check free disk space

I've been inserting data into PostgreSQL DB, and got the error message: psycopg2.OperationalError: could not extend file "base/16384/61892": No space left on device HINT: Check free disk space I am ...
Eric Kim's user avatar
  • 173
6 votes
2 answers
23k views

Getting Too many Open files error for Postgres

In Ubuntu 14.04 server on a JAVA application we are getting Too many Open files error for Postgres (Using Postgresql 9.5). We set the following in /etc/security/limits.conf * soft nofile 16384 * ...
Geo's user avatar
  • 575
6 votes
5 answers
10k views

Postgres Remote Access

I am trying to connect to postgres remotely.I have followed this tutorial http://www.cyberciti.biz/faq/howto-fedora-linux-install-postgresql-server/ and have executed the following commands to see if ...
Gandalf's user avatar
  • 245
6 votes
3 answers
12k views

How can I get access to password hashing in postgresql? Tried installing postgresql-contrib in ubuntu, still can't access hashing functions

So I'm trying to just hash some passwords in postgresql, and the only hashing solution that I've found for postgresql is part of the pgcrytpo package ( http://www.postgresql.org/docs/8.3/static/...
Kzqai's user avatar
  • 1,288
6 votes
1 answer
28k views

Cannot connect to PostgreSQL unix domain socket

I'm trying to connect to PostgreSQL Unix domain socket from a PHP web application. Relevant system components: CentOS 7 x64 (SELinux enforced) postgresql93 9.3.5-2PGDG.rhel7 nginx 1.6.2-1.el7.ngx php-...
Sergii Bogomolov's user avatar
6 votes
3 answers
400 views

Get the time spent by a process on Linux

I want to measure the time it takes to backup a PostgresSql database, I know I can do time pg_dump ...., but what if I already launched the pg_dump command?. The process already took more than half ...
Leonardo Ramé's user avatar
6 votes
1 answer
2k views

DB Full Vacuum, Cluster, Analyze - in which order?

I have a huge (~150 GBytes) postgresql database whose performance has dropped during the last weeks. In order to improve performance, I intend to perform FULL VACUUM, CLUSTER and ANALYZE. In which ...
Adam Matan's user avatar
  • 13.6k
6 votes
1 answer
4k views

Postgres suddenly slower and higher CPU usage

We're running a Postgres DB that's about 38GB, hosted on a 68GB EC2 instance. It's been humming along, with load at ~0.7 (on an 8 core machine) and little CPU usage until about 16 hours ago, when CPU ...
Zeppomedio's user avatar
6 votes
3 answers
5k views

Error starting postgresql server on ubuntu

I recently stopped the postgresql database (that was working perfectly), and after a couple of days, when I try to start it, I get the errors: 2011-01-03 23:56:27 CLST LOG: could not translate host ...
xain's user avatar
  • 677
6 votes
3 answers
41k views

sudo /etc/init.d/postgresql restart (how to chose which installation?)

I have two postgresql installations: $ sudo /etc/init.d/postgresql status 9.1/main (port 5432): online 9.2/main (port 5433): online I was used to restarting the postgres database doing a: $ sudo /...
Marcus Junius Brutus's user avatar
6 votes
1 answer
3k views

Using pg_dump when connecting via a service

I connect to my postgres server using psql "service=subscription". How do I use the service with pg_dump? I tried to do the following: pg_dump -h "service=subscription" > /home/fogest/dump.out ...
ComputerLocus's user avatar
6 votes
2 answers
6k views

How can I query the encoding of a postgresql cluster?

When you create a new postgresql cluster you can specify an encoding for the cluster, but I'm not sure to know how to retrieve it once the cluster has been created. I have seen that with \encoding ...
Jaime Soriano's user avatar
6 votes
2 answers
9k views

Using PGPASSWORD variable does not seem to work any more on Postgresql 9.3

In Posgresql you can set a variable called PGPASSWORD (and PGUSER) so you don't have to use password when using postgresql commands such as psql or pg_dump But I am a bit confused because I can't ...
Ulukai's user avatar
  • 919
6 votes
1 answer
4k views

Understanding PostgreSQL disk usage

I'm using 9.1 and have a table called dpi in a database with UTF-8 encoding currently sitting at 18,628 rows. There's a TEXT column in there called foto that holds B64 strings which represent a unique ...
rhyek's user avatar
  • 163
6 votes
1 answer
7k views

PostgreSQL raw query vs "Function returns TABLE" - insane difference in performance. Why?

I work with the PostgreSQL and it is used for reporting. The way it is configured currently is as follows: There is a complex query which returns report data, like this: select Column1 as Name1, ...
Evgeny's user avatar
  • 213
6 votes
1 answer
13k views

Strange high CPU I/O wait on postgresql server

I'm really struggling with a performance issue currently, where all my database queries takes up to a 1000 times more than normally, when my database server is under "heavy" load. I have a server ...
Niels Kristian's user avatar
6 votes
3 answers
5k views

Periodic spiky CPU usage by winlogon / LogonUI

I've noticed that my AWS server occasionally starts using a bunch of CPU for no particular reason, looking something like this: Observe that it does not occur at specific times, but has a very ...
RomanSt's user avatar
  • 1,217
6 votes
2 answers
3k views

pg_dump compression; Server or Client side

When you are specifying compression on pg_dump, is the compression happening on the server side so that the transfer is quicker or is the pg_dump doing the compression? Thank you
KoboldAtWork's user avatar
6 votes
1 answer
7k views

How can I set up an Amazon RDS for PostgreSQL database to act as a replica to a non-RDS PostgreSQL database?

I'm trying to set up an PostgreSQL database on Amazon RDS as a replica to another PostgreSQL database that lives on a regular server. Most of the instructions I've found online require shell access so ...
Dan Loewenherz's user avatar
6 votes
1 answer
2k views

32bit ODBC Postgres driver on Windows 2008 R2 x64

I'm trying to install the Postgres ODBC 32bit driver on a Windows 2008 R2 64bit machine. After installing it, with no errors, I go to the ODBC panel, the 32bit version under the /syswow64 folder and ...
Mia's user avatar
  • 201
6 votes
3 answers
5k views

Upgrade PostgreSQL 9.5 to 9.6 with PostGIS - pg_upgrade

I'm trying to upgrade a 9.5 server to 9.6, and pg_upgrade gives me the following error: Performing Consistency Checks ----------------------------- Checking cluster versions ...
Jeremy Wilson's user avatar
6 votes
2 answers
5k views

Use Letsencrypt certificates for Postgresql

I have successfully configured Letsencrypt for my NGINX webserver on Debian 8 Jessie. Now I have installed Postgresql 9.4 and want to use the LE certificates (in /etc/letsencrypt/live/mydomain/com/......
algielen's user avatar
6 votes
1 answer
12k views

How to configure rsyslog to log into PostgreSQL without too much latency?

I'm trying to configure RSyslog on a Debian machine to log everythingo into PostgreSQL, while also logging as usual on disk. I'm using a pretty stock Debian configuration and I activated the related ...
Jonathan Ballet's user avatar
6 votes
1 answer
4k views

Bad performance for a large join query in PostgreSQL 8.4.4, despite enough memory to cache entirely

Question How can I make the query described in this post faster, in particular by making PostgreSQL using the available RAM? - Note that I have tried to configure effective_cache_size and ...
ehsanul's user avatar
  • 427
6 votes
3 answers
2k views

Development, Testing, Staging and Production model

I'm developing a run of the mill django powered website with a Postgres database. I develop locally and have 3 VPS servers for Testing, Staging and Production. Each VPS runs their own Linux / Apache /...
oliland's user avatar
  • 163
6 votes
2 answers
2k views

How to change stack depth limits in /etc/security/limits.d/ and have the changes apply to services at boot

My system Series of Ubuntu 14.04.5 (x86_64) Servers, kept updated My application required I increase stack depth for postgres I created a file in /etc/security/limits.d/myapplication.conf The ...
James T Snell's user avatar
6 votes
4 answers
2k views

postgresql on Ubuntu 10.4 doesn't start on boot

I'm trying to have postgresql 8.4 start on boot on Ubuntu server 10.04 (64bit). First, I tried putting: su -c 'pg_ctl start -D /home/postgres -l /home/postgres/serverlog' --preserve-environment ...
vivri's user avatar
  • 203

1 2 3
4
5
40