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
0answers
2 views

Storage snapshots for consistent backup of postgresql - different data and log volumes

We are running many Linux VM's in a vmware/shared storage environment, each running its own instance of postgreSQL (a mix of 9.0 and 9.3). Currently, the entire VM sits on a single root ...
0
votes
1answer
19 views

Postgres ON DELETE Rule Not Working With WHERE Clause

I'm trying to implement "Soft Deletes" given the following schema: CREATE TABLE categories( id serial not null primary key, num integer, "name" text, deleted_at timestamp default null ); ...
0
votes
1answer
11 views

What postgres sync solution should I use to duplicate production, but read it from another application?

I'm a little out of my league with my current problem, but I'm looking for a solution where the following conditions are met: My production database reads and writes as it currently does My ...
1
vote
1answer
10 views

Using psql with string variable that has two periods

psql -d mydb -U me -h localhost -f db_log.sql -v db_user_string='Me' -v version="1.7.3" into the script begin; create table db_log ( db_owner varchar(255) not null, db_user varchar(255) ...
3
votes
1answer
7k views

Fixing table structure to avoid `Error: duplicate key value violates unique constraint`

I have a table which is created this way: -- -- Table: #__content -- CREATE TABLE "jos_content" ( "id" serial NOT NULL, "asset_id" bigint DEFAULT 0 NOT NULL, ... "xreference" varchar(50) ...
0
votes
0answers
3 views

How to transform Streaming Replication Master-Slave to Cascaded Master-Slave1-Slave2 setup?

I've setup streaming replication as part of HA solution on my production enviroment. I've two ubuntu servers PROD1 and PROD2. PROD1 is master and PROD2 is slave. In case of failover the slave database ...
0
votes
1answer
22 views

From PostgreSQL to SQLServer (No User/Pass)

Some of you MAY be able to crack a really tough nut... I have to move all the data from a PostgreSQL (9.1 - Windows) database to SQL Server because the company I am contracting with is upgrading the ...
0
votes
0answers
6 views

Data base design for handling website subscriptions

I have a website where at least 20 posts are posted in a day and each post contains more than 5 categories and each category has further tags. I wanted to build a subscription module that users can ...
4
votes
1answer
96 views

PostgreSQL operator uses index but underlying function does not

I'm attempting to use JSONB with JDBC, which means that I have to avoid any of the operators which use the '?' character (as the PostgreSQL JDBC driver has no escaping for this character). Taking a ...
1
vote
3answers
2k views

psql: Show current database

In psql I can use \c <database_name> to connect to a given database. How can I see what database I am currently connected to? When I do: my_db> current_database(); I get: ERROR: ...
0
votes
0answers
14 views

FATAL: terminating walreceiver due to timeout

1/ DESCRIPTION: Machine 1 (slave): Centos 6.6 , x64 , installed PostgreSQL 9.3 (on Local) Machine 2 (master): Centos 6.6 , x64 , installed PostgreSQL 9.3 (on Cloud) Machine 1 (slave) and machine ...
2
votes
2answers
28 views

UNIQUE constraint on large VARCHARs - PostgreSQL

I have a column defined like data_url character varying(32768) NOT NULL and UNIQUE constraint on that column CONSTRAINT unique_data_url UNIQUE (data_url) When large object is being inserted into ...
2
votes
2answers
33 views

PostgreSQL CHECK constraint is evaluated wrongly

Following schema allows to violate the CHECK constrait, but only once. Then the constraint is correctly enforced. drop database if exists example; create database example; create table example_table ...
0
votes
1answer
38 views

Random unguessable primary key which preserves correct order

I want primary keys in my table to be random and unguessable while still preserving correct order (order in which documents were inserted to database). Firebase uses this algorithm: ...
1
vote
1answer
361 views

How can I get data from broken PostgreSQL server? [on hold]

I have installed CentOS on a virtual machine and installed the latest version of PostgreSQL server 9.3. I have added PostGIS extension and some data. For some reason, the PostgreSQL service now fails ...
0
votes
0answers
21 views

OCIEnvCreate failed to create environment handle after installing oracle_fdw

I'm want use Postgresql 9.3 in CentOS 7 and add extention oracle_fdw. but when use this extenstion i get error OCIEnvCreate failed to create environment handle after installing oracle_fdw. my ...
0
votes
1answer
43 views

Pros and cons of using many schemas in PostgreSQL as opposed to just one?

For a large SAAS application (backed by PostgreSql 9.4), with over 300,000 accounts (and growing), what are the pros and cons of using a schema per account to partition the data vs putting all the ...
0
votes
1answer
37 views

Select rows from table where each row refers to a different schema

I have customer table in public schema. And I have a schema named after each customer. Each customer schema contains different tables like portals, plugin, ... When I want to select portals used by ...
0
votes
1answer
619 views

pgpool2 setup on master-slave replication using postgreql 9.1

I have already setup 2 ubuntu server 12.04 LTS on my local machine using virtual box and is using the master-slave replication (postgresql 9.1). 192.168.0.109 = master 192.168.0.110 = slave my ...
0
votes
0answers
27 views

11.2GB text file import MySQL or PostgreSQL

I am out of my depth a bit perhaps but submit this query hoping someone has some experience importing large (11.2GB) tab delimited text file (2.2GB tar.gz file) into either MySQL 5.5.41 or PostgreSQL ...
2
votes
1answer
108 views

How can I identify queries that are producing cache misses in Postgres?

I have a Postgres database with a total data size of 115GB. The server has ~60GB of memory. The index cache hit rate is holding at 99%+ but the table cache hit rate has fallen to ~97%. I am trying ...
7
votes
2answers
1k views

Migrate heroku database to Amazon RDS with minimum downtime

I have a heroku postgres database and want to migrate it to Amazon RDS to save cost. What's a way to do so with minimum downtime? Usually this involves replicating database in real time and then ...
1
vote
1answer
1k views

PgPool 2 with HA Proxy

So, I'm looking to add high availability to a postgresql server. I've looked into some tutorials on the web but, haven't been able to find exactly what I'm looking for. E.g.: PostgreSQL ...
2
votes
1answer
62 views

PostgreSQL 9.x: a clustered index on column A is the same as creating a table ordered by A?

Reading the official PostgreSQL documentation for version 9.0 I read an interesting escamotage that performs better than CLUSTER for big tables: The CLUSTER command reorders the original table by ...
0
votes
2answers
43 views

How to rollback transaction if row changed?

I am using Postgres 9.3. When I update row in a table my flow looks like this: Fetch old row (ex. SELECT * FROM tbl WHERE id = 1) Validate new data in scope of old data (old row fetched in point 1) ...
0
votes
1answer
6 views

extract state changes from snapshot-based archive table

I have a table snapshot, storing snapshots every time an object stored in another live changed one of its attributes. I need to find all objects that changed a specific variable to a specific value in ...
1
vote
1answer
99 views

How to get back the PostgreSQL database to a specific poin in time?

I am running a batch script(10:40) in postgresql database which will insert values in differnt tables on postgresql till (11:00).Later i came to know that the script was executed mistakenly.Now how ...
0
votes
1answer
619 views

Configuring Postgres 9.2 streaming replication with Redhat Clustering Suite for High Availability

I'm in the process of configuring postgresql 9.2 streaming replication with Redhat Clustering Suite for high availability. I have 1 master and 1 hot standby configured to replicate the data from ...
-2
votes
1answer
24 views

Best way to query for a list and then run a query for each result in that list [on hold]

I am new to databases in general and particularly new to PostgreSQL (v 9.3). I often find myself in the situation that I first run a select statement which will return a list of users stored in an ...
1
vote
1answer
227 views

REST webservice as a Foreign Data Wrapper in postgresql

I know it's possible to embed data from a foreign resource in a postgresql database as a foreign table and have seen examples including redis, another postgres database, etc but I can't really find ...
0
votes
2answers
27 views

Write output from dynamic queries in PL/pgSQL to CSV file

PG_CMD="psql -d portal -U portal -c " PG_CMD_TP="psql -d portal -U portal -t -P format=unaligned -c " abc() { $PG_CMD " DO \$$ DECLARE srowdata record; ...
3
votes
2answers
142 views

Slow fulltext search due to wildly innacurate row estimates

Fulltext queries against this database (storing RT tickets) seem to be taking a very long time to execute. The attachments table (containing the fulltext data) is about 15GB. The database schema is ...
0
votes
0answers
12 views

Imported a new month of data, now getting Numeric data overflow error when running query

We have an ETL job that brings in monthly revenue, and I've never experienced any issues with the data in terms of not being able to return results. The ETL job succeeded, but when I run a query to ...
1
vote
1answer
78 views

How to put the results of a query into a variable?

I have written a Postgres anonymous block. In which I have written join query on multiple tables like: select a.portal.*, a.p_fb_config.*, a.p_gplus_config from a.portal left outer join ...
1
vote
1answer
535 views

Tree structure hierarchical query sorting

I have the following structure of table. CREATE TABLE table1 ( serial_num integer NOT NULL, parent_num integer, parent_key text, key_ary_num integer[], level integer, rank integer, ...
3
votes
1answer
53 views

How can I implement a sequence for each foreign key value?

In a PostgreSQL (9.3) database, I have a one-to-many relationship between Accounts and Customers, so within the Customers table, I have a globally-unique id for the primary key of each Customer, plus ...
2
votes
2answers
28 views

query to find all dblink()

Besides manually searching for all the dblink() used in triggers/functions/stored procedures is there a way to query for this information? Example: There is a dblink inside of a trigger, can I select ...
1
vote
0answers
21 views

Pass a URI as a pgAdmin parameter

I can connect to Heroku with psql passing a URI: $ psql postgres://user:password@ec2-54-243-xxx-xxx.compute-1.amazonaws.com:5432/d7xkxididkdi I want to do the same with pgAdmin: $ pgadmin3 -qc ...
0
votes
0answers
11 views

Postgres Join that allows for an empty table [migrated]

I have the following schema, and I'm having a lot of trouble joining these tables properly. I'm trying to get all folders that don't have a file updated in the last 10 mins in them. But I want to ...
0
votes
0answers
7 views

Schemas with the same name in a Redshift cluster?

Is it harmful in some way to have multiple schemas with the same name in a database cluster, specifically Redshift? I want to create multiple databases, and then create a schema "tables" or "data" or ...
1
vote
1answer
28 views

How compiled are prepared statements?

I found this explanation of how prepared statements are compiled, but I'm still unsure. What exactly is "compiled" in the context of a prepared statement? Is a C compiler executed to create a custom ...
0
votes
0answers
15 views

SQL social connections query

Given the following 2 tables: Profiles --------- id, name Connections ----------- id1, id2, status where status is (for the sake of the example) either 'pending', or 'active'. What I'm trying to ...
0
votes
1answer
15 views

Avoid PG::TRDeadlockDetected

I am using the below function in a bulk insert, usually the bulk insert has around 60 rows and each row will have the below function. But from time to time I get PG::TRDeadlockDetected: ERROR: ...
1
vote
0answers
41 views

Simple avg query on large table much slower in PostgreSQL than SQL Server

I have a database with three tables items, parameters and measurements in both servers and want to query the measuerment table. But the query is much slower in PostgeSQL (9.4) vs SQL Server (2012). ...
2
votes
0answers
27 views

Get inserted row count when master table isn't modified

I have a partitioned table where data resides only in the child tables. The master table has an insert trigger that will distribute inserted data into appropriate child tables. Now, I need to create ...
1
vote
2answers
28 views

Speed of WAL send/receive when replicating

1/ Description: I have a cluster include two machines Master: postgresql 9.3 run on Centos Slave : postgresql 9.3 run on Centos Master ----> Slave : relpica from Master to Slave by asynchronous ...
0
votes
0answers
9 views

Failover_command in pgpool is not working?

I have setup the pgpool with load balancing and failover mode. Following is my failover_command='/usr/local/etc/failover.sh %d %P %H %R' The following is the script: !/bin/bash -x FALLING_NODE=$1 ...
1
vote
1answer
103 views

How do I use pg_dump and pg_restore to move a large database to another machine?

I'm a bit unclear on how to use the pg_dump/pg_restore duo to move a large-ish database. Here is the command I use to dump the database (the host and username are taken from environmental variables): ...
-2
votes
1answer
31 views

How to use variable value inside copy function [closed]

I want to send data to .csv file using COPY function in Postgres. But I am using anonymous block so my table name should be value of variable. COPY (SELECT cname.portal from user) To '/tmp/out.csv' ...
0
votes
1answer
17 views

Postgres Nested WHEN Aggregate Function

In PostgreSQL (version 9.4) I am trying to construct a query to determine in various tranches how many times various numbers appear in the data set between ranges. When I query group-ing by ...