PostgreSQL is an enterprise class, open-source, object-relational database management system (ORDBMS) available for all major platforms including Linux, UNIX, Windows and OS X. Please mention your exact PostgreSQL version when asking questions.

learn more… | top users | synonyms (1)

1
vote
1answer
365 views

Partial vacuum and whole table vacuum

From postgresql document, I know vacuum can skip pages that are already marked in visibility map, so vacuum progress only vacuum pages which requires vacuum,and we call this partival vacuum; ...
1
vote
1answer
155 views

Postgres table size doubles are transaction completes

I have a table with 50 million rows and 40+ text columns. Size of the table is around 17GB. Inside a transaction, I add another integer column to the table and update all the rows with corresponding ...
1
vote
1answer
516 views

PostgreSQL vs MySQL benchmarks when a 2nd level cache is included

Has anyone been able to find any benchmarks comparing PostgreSQL and MySQL performance when they are used by a system through Hibernate (or another comparable ORM) and the 2nd level cache (backed by ...
1
vote
1answer
2k views

pg_restore: [archiver] unsupported version (1.11) in file header postgresql

My server is working on Ubuntu with PostgreSQL 8.4 and the development machine I'm using has Debian Lenny with PostgreSQL 8.3. My machine can't be upgraded to PostgreSQL 8.4 and I need to restore a ...
1
vote
1answer
776 views

Tutorial for installing and setting up 'streaming replication' with PostgreSQL on Windows XP

Please refer to the diagram in the link below. While looking at my goals. http://i.stack.imgur.com/GkVWb.png GOAL: Get PostgreSQL 9.1 running on 3 terminals on the 'on site' location. 'on ...
1
vote
1answer
736 views

How to know how many lines are copied from table - psql

I want to write a script to backup a table using COPY. psql "connection parameters" -c "COPY (SELECT * FROM tbl WHERE insertion_date > 'date') TO STDOUT WITH CSV HEADER;" | bzip2 -c > ...
1
vote
1answer
437 views

PostgreSQL: Trouble accessing another server's Data via NFS

Background: I'm working on a 2-node High Availability PostgreSQL database cluster for my company. The system has a master/standby setup where the standby takes over the duties of the master when the ...
1
vote
1answer
104 views

Impact of changing schema name

I have a server with two databases. Each database has a different schema name despite the fact that DB2 is a subset of DB1 (DB2 has a subset of the tables in DB1). Not withstanding any code changes ...
1
vote
2answers
1k views

Postgresql 9 table lock resolution

I have an ALTER TABLE statement that according to pg_stat is "waiting", and I'm wondering if there is an easy fix to allow the statement to execute? From my limited understanding I think it's a ...
1
vote
1answer
224 views

Slony not replicating changes from a dump file

I’m working on a project where we are using pg_dump to get the data out of one database and then using psql to load it into another. The second database is the master db in a Slony replication ...
1
vote
1answer
916 views

pg_restore fails for a pg_dump from a db that worked just fine

Any idea how this can happen? I have a table which I created by loading data into it via the COPY command. A column in it was declared as VARCHAR(200). I dumped the table, moved it to another ...
1
vote
1answer
213 views

postgresql - transaction log - versioning?

Is it possible to "version" your database so that any change can be reviewed or roll backed? Obviously backups are important but being able to go back to any previous state of the database (at least ...
1
vote
1answer
310 views

Using PL/PerlU with a non system perl lib

psql -1f check_email.pl.sql ccushing postgres SET psql:check_email.pl.sql:7: ERROR: Can't locate Email/Valid.pm in @INC (@INC contains: /usr/lib/perl5/site_perl /usr/share/perl5/site_perl ...
1
vote
1answer
1k views

64 bit Postgres 9.1 Limitions on Windows 2008 R2?

When Deploying Postgres on Windows Server 2008 R2: Are there any limitations on windows that don't exist on Linux? Does the postgres scale in the same way on windows as it does on Linux? Is ...
1
vote
1answer
804 views

PostgreSQL 8.4 hangs on create table, reindex

I have a rather small database(200Mb) which was running on ubuntu 10.10/amazon ec2 fine for a year almost but today I ran into the following problem: When I create a table that references other table ...
1
vote
1answer
266 views

How to generate PostgreSQL COPY bin files?

My Googlefu is failing me. Are there any code snippets, either in Python or FORTRAN, that shows how to create binary files to be used by the COPY command in PostgreSQL?
1
vote
1answer
20 views

GIN index not used when adding order clause

I'm trying to speed up a query that executes three ILIKE queries and reduces these with or (returning the overall count and 10 entries) SELECT *, count(*) OVER() as filtered_count FROM "users" ...
1
vote
0answers
10 views

MATLAB® export double numbers into PostgreSQL by `datainsert` (of Database Toolbox)

How is it possible to pass double numers into numeric postgreSQL columns from MATLAB? Here the list of matlab data: var1 197x1 cell var2 197x1 double var3 197x2 double var4 197x2 double ...
1
vote
0answers
4 views

Generate tables from entities with hibernate

I'm using postgresql with a spring/hibernate to create a multi-tenant application where every company will have its own seperate schema and tables in the database. So when the super administrator ...
1
vote
0answers
34 views

3 tables joins performance issue

DB: Postgres 9.4 schema: table a: id, score table ab: a_id, b_id, b_type table b: id, name, type I am trying to get top 10 result in the following format: name, count(group by name), ...
1
vote
0answers
20 views

Postgresql FDW oracle out of memory

I have a linux box with centos 6.7 and postgres 9.3.9. I have a foreign data wrapper throught an oracle 11.2 box and a jdbc driver. It's working fine since several months, but yesterday I had to ...
1
vote
0answers
20 views

Connection Failure for postgres at Talend Open Studio

I have tried to connect postgresql database in Talend open studio. After giving login, password, port and server, i have issued check. Its saying "Connection Failure. You must change the DB Setting". ...
1
vote
0answers
7 views

postgresql: deleting from a view

In Postgresql 9.4, we're doing a view as a UNION of to tables A and B, and we'd like to have a single DELETED N ROWS message when we 'delete' the records in the underlying tables (via a a trigger on ...
1
vote
0answers
22 views

Postgres 9.4 on kubuntu 14.04 won't start

I've had postgres9.4 running without incident for around 5 months. Today after a restart I can't get postgres running. When I open pgAdminIII and try to connect to my server I get "Server doesn't ...
1
vote
1answer
45 views

What kind of database should I use?

I'm designing a new system and I'm wondering what kind of DB should I use for my needs. I have a tree structure of nodes, where each node has historic values. For each historic value it should hold 2 ...
1
vote
0answers
25 views

Replication of specific tables using Postgres 9.4.4 and Amazon Web Services

I have currently setup a Postgres 9.4.4 RDS instance on Amazon Web Services. On this instance I hold 20 tables. In addition I also have Postgres 9.4.4 installed on my local server (OS: 64-bit Linux) ...
1
vote
1answer
28 views

PostgreSQL 9.3 is not returning existing rows with Ruby on Rails or Squirrel

I dumped an existing PostgrSQL database (generating "Insert" statements), and imported it on another computer. The source runs Debian (UK) OS, the target Mint (FR) OS. For some tables, when I try to ...
1
vote
0answers
31 views

PostgreSQL 9.4.4 Crashing

We are having 9.4.4 version running on linux( PostgreSQL 9.4.4 on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.8.3 20140911 (Red Hat 4.8.3-9), 64-bit) which is crashing unexpectedly and coming ...
1
vote
0answers
32 views

PostgreSQL trigram GIST vs. GIN indexes

I have a PostgreSQL 9.1 database that contains 10M+ rows and some text fields that need similarity and %word%-like searches, so I decided to use trigram indexes. Initially, I started to use GIN ...
1
vote
0answers
21 views

Do you need external caching for a Postgres database?

I am currently building a server and have just written a database connection using JDBC, Jooq, and added a DAO layer, which also handles transaction management. I remember that databases can cache ...
1
vote
0answers
20 views

Postgres with no clients still chatty on network

This may be a non-issue but I was doing some network monitoring on my Mac and noticed that PostgreSQL (9.4.4) was still sending and receiving packets even when I'm not aware of any clients (localhost ...
1
vote
0answers
30 views

Query planner slow to use newly created index on database under heavy load

We have some heavily loaded databases with more than 1k requests / sec and have started to have problem with index maintenance. Sometimes we need to update/recreate one of the indexes we have, for ...
1
vote
0answers
17 views

Pass additional data in connection for use by PGSQL Trigger

I would like to add some auditing triggers to PG, to log all changes to tables. I have found an example trigger ( wiki.postgresql.com - 9.1+ audit trigger ), which gets me started. What I would ...
1
vote
0answers
42 views

Splitting logging into a separate database

In a postgresql database for a asp.net mvc website I have 3 tables (visits, uservisits, hits) that have update/insert query's running on them on every pageview. Rows in visits (for all visitors) and ...
1
vote
0answers
22 views

Use pg_dump with postgis extensions?

I'm using Postgres 9.4. I'm trying to dump a database (both the schema and the actual data) with the PostGIS extensions (set up using CREATE EXTENSION): $ pg_dump prescribing -U prescribing -h ...
1
vote
0answers
13 views

To minimize Cache misses in PostgreSQL?

You can calculate cache misses as described here. However, I am interested in how to minimize the phenomenon. I have some algorithms that are based on hash tables, which cause much cache misses by ...
1
vote
1answer
29 views

Can't create user in postgres

When I run CREATE USER username WITH PASSWORD 'test' in sudo -u postgres psql and run \du only postgres user appears... I'm not prompted anything when running commands like ALTER USER and I know USER ...
1
vote
0answers
26 views

SymmetricDS scheduled sync

I am trying to configure SymmetricDS to run a scheduled replication from one postgres database to another. I've seen people suggesting doing that by setting these fields in node config: ...
1
vote
1answer
48 views

Count childs of parent with sql or save in column?

In postgres I have a threaded setup where parent id has many childs and childs can have childs. What is the most efficient way to get the count with a sql statement similar to this: WITH RECURSIVE ...
1
vote
0answers
29 views

How to represent multiple unique constraints across hierarchical tables?

My (shortened) table definition is: CREATE TABLE bus ( id integer NOT NULL PRIMARY KEY, name character varying NOT NULL CHECK (name<>'') ); CREATE TABLE network_node ( id ...
1
vote
1answer
17 views

Use of smallserial gives error with PostgreSQL

I know the very basics of databases. I worked in the past with MySQL and recently switched to PostgreSQL (9.1) with PostGIS 2.0.1. At the moment I'm designing a new database and I use the tool ...
1
vote
0answers
42 views

Postgres - Error while adding constraints

Can anyone tell me what this error is? And How to resolve it? ERROR: could not read block 27651 in file "base/114689/118316": read only 4096 of 8192 bytes CONTEXT: SQL statement "SELECT ...
1
vote
0answers
39 views

Single Redshift table vs several mysql/ pgsql (on RDS) tables for small (200 tables * 20 columns * 1 million) monthly aggregation data

We're providing SaaS Data Analytics Dashboard for around 200 clients. We're using Amazon Redshift for storing data in in around 5 tables. We're getting around 30 million hits per day. Currently our ...
1
vote
0answers
131 views

Generally unsolvable problem? How to ORDER BY aggregated columns GROUPED BY many possible columns very fast?

Is there general solution or some best practises or at least some vague hint, how to solve following problem (general, but concretised in following example)? I think this must have been solved many ...
1
vote
0answers
17 views

PostgreSQL: native library learn the filesystem path that was used to load it?

From the _PG_init of a dynamic library that postgres has loaded (either because of a function declaration referring to it, or just LOAD pathname; in SQL), is there any call I can make, not ...
1
vote
1answer
55 views

DB design for employee working hours constraints [closed]

I have a simple employee schedule database (I use PostgreSQL): employees: - id - name - email shifts: - start - stop - employee_id Now there is need to apply constraint for working hours. ...
1
vote
1answer
30 views

Procedure to read from crontab like table and populate another one periodically

Postgres 9.3 Debian 7 I'm working on a mobile game project (poker like) where an admin user can register a recurrent tournament. As it's recurrent, I modeled it crontab like as follows. ...
1
vote
0answers
62 views

Having problems trying to back up a Postgres database from AWS RDS

I am trying to backup a Postgres database from RDS and I am getting this error message. pg_dump: [archiver (db)] query failed: SSL error: unexpected message pg_dump: [archiver (db)] query ...
1
vote
0answers
34 views

PostgreSQL suddenly using all my disk space

Today I fired up my linux mint via my virtual computer (host is windows 8), left for 30 minutes, and when I was back, I got an error saying I was out of disk space. Yesterday I had about 20 free GB ...
1
vote
0answers
36 views

How to use variables in a PL/pgSQL function with dynamic queries?

...and when must I use EXECUTE ... quote_ident(variable)? Taking an input to the function as text and storing part as a variable: wtype := split_part(message, ' ', 4); realval := ...