0
votes
0answers
12 views

Postgres 9.3 on Linux - Resource utilization - Multiple vs one service services

I'm consolidating multiple Linux boxes running Postgres into one box. Given the way Postgres handles resource utilization, would one single server service handling all the databases provide a much ...
0
votes
0answers
183 views

Tons of “invalid command \N” when restoring a dump

I'm following these instructions to upgrade postgres from 9.1 to 9.3: http://www.postgresql.org/docs/9.1/static/upgrading.html When I restore (Step 8), I get a ton of messages like this: ...
0
votes
1answer
50 views

trying to install postgresql-contrib for pg 8.4 in Ubuntu 12.10

I had to do some strange voodoo last year sometime to get postgresql 8.4 installed on a relatively modern ubuntu OS. I had to use 8.4 because the production site is 8.4 and we needed to ensure ...
1
vote
3answers
104 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 ...
1
vote
0answers
144 views

postgresql: invalid authentication method “pam”: not supported by this build

When I try to start postgresql 9.3.1 I receive this in startup log: # cat /var/lib/pgsql/pgstartup.log LOG: could not create IPv6 socket: Address family not supported by protocol LOG: invalid ...
0
votes
0answers
367 views

PostgreSQL log warnings: open temporary statistics file, pgstat wait timeout

We frequently get the following warnings with our Postgresql Database LOG: could not open temporary statistics file "pg_stat_tmp/pgstat.tmp": No space left on device WARNING: pgstat wait timeout ...
1
vote
1answer
113 views

One-line backup, compress, scp database file

I support a company with a remotely hosted web site. They use PostgreSQL dbs. I can't write scripts on the remote server, and I don't want to store any backup files on the remote server, but I can ...
-1
votes
1answer
266 views

Unable to login in phpPgAdmin

I am trying to login to phpPgAdmin from a remote client. I've created the user root, but when I login phpPgAdmin returns an error message saying "Unable to login" When I check the log it says: ...
1
vote
1answer
330 views

“Could not create IPv6 socket” postgresql standby error

I'm trying to create Postgresql 9.1 hot-standby using following steps: Configured 2 virtual linux machines. Master's ip: 10.10.10.1, Standby's ip: 10.10.10.2. Ping test passed. Restored the same db ...
1
vote
1answer
38 views

Proper postgres port forwarding on linux/ubuntu inside AWS VPC

At Amazon AWS we have VPC, where one node has public IP and others - private. Instance with public IP is something like balancer. On every node we have Ubuntu LTS. One of the private nodes is ...
0
votes
2answers
193 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 ...
0
votes
1answer
231 views

Understanding postgreSQL's indent, md5, password in pg_hba.conf

If I have the following in my pg_hba.conf # TYPE DATABASE USER CIDR-ADDRESS METHOD local all all trust local all all ...
1
vote
2answers
338 views

Dependency issues installing postgresql

Here is the steps I've followed to install postgresql on Debian server: add backports deb http://backports.debian.org/debian-backports squeeze-backports main contrib non-free to ...
0
votes
1answer
372 views

Password authentication failure in PostgreSQL 9.1 from remote client

I run PostgreSQL 9.1.9 in Debian Squeeze server (using backports). The contents of pg_hba.conf file is: local all postgres trust # TYPE DATABASE ...
0
votes
1answer
67 views

Linux server responds to postgresql querys and ssh only when pinging the server

I have a problem. My developers are using one of my local servers that is located in our office. The server is running PostgreSQL and when they try to make a query to it, it just lags and then gives ...
1
vote
2answers
103 views

PostgreSQL 8.1 out of space and stopped, data directory is empty

I used PostgreSQL 8.1 on Gentoo/Linux 2.6.14r5. My db server's disk space looks like below: db postgresql # df -l Filesystem 1K-blocks Used Available Use% Mounted on /dev/sda3 ...
-1
votes
2answers
1k views

PostgreSQL server failed to start: request for a shared memory segment exceeded your kernel's SHMMAX parameter

I increased PostgreSQL's shared buffer (among other settings) to 4096M and now PostgreSQL fails to start, giving the error message below. Should I change the kernel's SHMMAX parameter to 4096M? The ...
4
votes
3answers
1k views

PostgreSQL slow commit performance

We are having some problems with a PostgreSQL configuration. After some benchmarks I found out that very simple queries are taking a relatively long time, upon furter inspection it appears that the ...
2
votes
1answer
488 views

Postgresql unintended upgrade to 9.2.3

OK, so I'm relatively new to linux. Some months ago, I managed to set up a website using Amazon's build of CentOS, PHP and Postgres 9.1. I'd done an initdb on /pgdata which was mounted on an Amazon ...
-1
votes
1answer
1k views

Why is Postgres not starting? [closed]

Somehow my Postgres is not anymore starting: # psql psql: could not connect to server: No such file or directory Is the server running locally and accepting connections on Unix domain socket ...
1
vote
1answer
1k views

Postgresql unrecognized, even though it's installed

Postgresql 8.4 seems to be installed properly, according to this: sudo yum install postgresql Loaded plugins: product-id, refresh-packagekit, rhnplugin, security, subscription-manager This system ...
1
vote
1answer
2k views

How to calculate max_connections for PostgreSQL and default_pool_size for pgbouncer?

Is there a rule or something I can use to calculate a good number for max_connections, default_pool_size and max_client_conn? The defaults are odd. PostgreSQL defaults to max_connections=100 while ...
0
votes
1answer
310 views

Setup HA postgres database, failover concept

I want to setup a HA postgres database. I setup master and slave database with synchronous streaming replication on different physical servers. When there is an error in the replication, the slave db ...
2
votes
1answer
134 views

Why doens't my Postgres user have permissions to add a Postgres database?

First, I ran: sudo su postgres createuser -U postgres foouser -P which worked fine, and I ran: createdb -U foouser -E utf8 -O foouser foodatabase -T template0 and got "permission denied: cannot ...
1
vote
1answer
131 views

selinux boolean allow_user_postgresql_connect has no effect

I'm setting up a new system which contains a postgresql database and I'm trying to lock down which machines are able to open a connection to the database. I'm trying to use the ...
1
vote
3answers
860 views

“postgres blocked for more than 120 seconds” - is my db still consistent?

I am using an iscsi volume on an Open-E storage system for several virtual machines running on a XenServer host. Occasionally, when there is a very high disk I/O load on the virtual machines (and ...
1
vote
1answer
386 views

How to move tablespace to another directory?

Let's say I have tablespace A located in the directory /A1, and I would like to move it (everything) to directory /A2? I use openSUSE + PostgreSQL 9.1, and if this simplifies anything -- I can stop ...
1
vote
3answers
328 views

PostgreSQL disaster recovery options

My customer has quite a large (the total "data" folder size is 200G) PostgreSQL database and we are working on a disaster recovery plan. We have identified three different types of disasters so far: ...
2
votes
1answer
255 views

Postgres child process eating up a lot of memory

We have a postgres 9.0 instance that is running on a pretty beefy machine (96G of RAM/24 cores). IN the recent weeks we have experienced crashes due to child processes of postgres being killed with ...
1
vote
2answers
3k views

pg_dump not working - do I need to change order of $PATH?

I'm trying to set the $PATH to pick up the latest version of pg_dump as I'm currently getting a mismatch error while doing a migrate in my Rails app (I recently changed the schema type to SQL). I ...
0
votes
1answer
376 views

Compilation error installing Nominatim

I am trying to install Nominatim from its source on github. PostgreSQL 9.1 and PostGIS 2 has already been installed using yum. The system is running CentOS 6.3 with cPanel. git clone --recursive ...
0
votes
1answer
428 views

rpm -Uvh & yum install

I am new to linux and wants to understand the reason for using some commands. I am trying to install PostgreSQL on CentOs by following the instructions here. First we do rpm -ivh ...
1
vote
0answers
277 views

pgsql troubles on centOS 5 with directadmin

I'm having trouble configuring pgsql for PHP 5.2 on the server I administer. It's a DirectAdmin enabled server, and the contents of ap2/configure.php5 are: #!/bin/sh ./configure \ --with-apxs2 \ ...
1
vote
1answer
231 views

Configure Postgres on Server A to pg_restore onto Server B (requiring certificate authentication)

I'm trying to configure a PostgreSQL instance running on SERVER_A to connect to another PostgreSQL instance on SERVER_B. I'm running a shell script on SERVER_A that ultimately needs to connect and ...
0
votes
1answer
408 views

How to get rid of “Could not connect to database” with Linux and libnss-pgsql2?

I use libnss-pgsql2 in order to have virtual system users stored in a PostgreSQL database. The virtual users in the database work just fine. They can log in. I can see their uid, gid, groups via the ...
10
votes
3answers
3k 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 ...
2
votes
1answer
364 views

Can't connect to PSQL Database on a VM from my Mac

Trying to connect to my PSQL database on my CentOS VM from my Mac using pgAdmin3. Getting the following error: Access to database denied The server doesn't grant access to the database: the server ...
7
votes
3answers
1k views

Postmaster uses excessive CPU and Disk Writes

using PostgreSQL 9.1.2 I'm seeing excessive CPU usage and large amounts of writes to disk from postmaster tasks. This happens even while my application is doing almost nothing (10s of inserts per ...
6
votes
4answers
3k views

iotop shows kswapd0 at 99.99% with 0% DISK READ and DISK WRITE

I have a heavy postgres query. It takes over ten minutes to run. I'd like to upgrade my hardware to get it run faster. I think more RAM will help, but my motherboard is full so I'll need a whole ...
3
votes
4answers
3k views

Connect to MySQL trough command line without need root password

I'm building a Bash script for some tasks. One of those tasks is create a MySQL DB from within the same bash script. What I'm doing right now is creating two vars: one for store user name and the ...
0
votes
1answer
148 views

Why does a Postgres query have drastically different performance on two modern machines?

I have two machines: Machine #1: Desktop Intel E5200 CPU (2.5Ghz) 2GB RAM (DDR2) Spinning hard drive (OS) and SSD Hard drive (data) Machine #2: Laptop AMD E-300 (1.3GHz) CPU 8GB RAM (DDR3) ...
5
votes
3answers
93 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 ...
0
votes
1answer
1k views

Strange permission errors in new PostgreSQL installation

A freshly installed PostgreSQL (with configuration overwritten) won't start: $ sudo service postgresql start * Starting PostgreSQL 9.1 database server * Error: could not read ...
0
votes
1answer
676 views

Error in osm data data import while nominatim installation

I am doing nominatim installation on my server. I run the following command ./utils/setup.php --osm-file /usr/share/osm2pgsql/planetfile/planet-latest.osm.pbf --all to import the pbf file and Few ...
5
votes
3answers
18k views

How to set shmall, shmmax, shmni, etc … in general and for postgresql

I've used the documentation from PostgreSQL to set it for example this config: >>> cat /proc/meminfo MemTotal: 16345480 kB MemFree: 1770128 kB Buffers: 382184 kB ...
0
votes
1answer
343 views

Installing multiple instances of postgres on suse

Is there a HOWTO out there somewhere that explains how to install multiple instances of Postgres on a SUSE server? Im building a test environment and need to have about 2 or 3 instances of postgres ...
0
votes
2answers
625 views

Installing Postgresql 8.3.7 in Debian Squeeze

I am tring to install postgresql-8.3.7 into Debian Squeeze OS. It is throws me following error. unmet dependency libkrb53, libpq5 . Whether postgresql-8.3.7 is compatible with Debian Squeeze ...
1
vote
2answers
392 views

How to convert Mysql database to Postgresql in Linux?

I have a large Mysql database containing tens of tables which I need to convert to Postgresql. I have tried a couple of ancient Perl scripts which I found here and there but none of them did the job ...
1
vote
2answers
164 views

How to dump database to be used with no credentials

I am wondering how can I generate a dumped database so that it can be restored without requiring the original database username and password? Ideally I'd like to know the pg_dumpall as well as ...
1
vote
1answer
550 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 ...