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,969
questions
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 ...
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 ...
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 ...
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 ...
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: ...
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 ...
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(...
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 ...
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 ...
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 ...
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 ...
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 ...
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, ...
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.
...
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. ...
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 ...
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 ...
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 ...
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 ...
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 ...
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 ...
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 ...
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 ...
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 ...
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
* ...
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 ...
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/...
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-...
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 ...
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 ...
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 ...
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 ...
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 /...
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
...
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 ...
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 ...
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 ...
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, ...
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 ...
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 ...
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
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 ...
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 ...
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 ...
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/......
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 ...
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 ...
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 /...
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 ...
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 ...