PostgreSQL 9.3 : 2013 release of PostgreSQL

learn more… | top users | synonyms

0
votes
1answer
16 views

Improve performance on concurrent UPDATEs for a timestamp column in Postgres

I am using a timestamp column called updated_at for cache invalidation. More information on this particular technique here. The queries all have the same format UPDATE "managers" SET "updated_at" = ...
0
votes
0answers
38 views
2
votes
1answer
25 views

Prevent postgres function of running more than 1 instance at the same time

DBMS: Postgres 9.3.4 OS: Debian 7 I have a plpgsql function that will be put in crontab to run every 15 minutes. The function should finish in about 8 minutes, but just in case it takes more than 15 ...
2
votes
0answers
33 views

Fast hamming distance queries in postgres

I have a large database (16M rows) containing perceptual hashes of images. I'd like to be able to search for rows by hamming distance in a reasonable timeframe. Currently, as far as I properly ...
0
votes
1answer
45 views

Streaming replication Postgresql 9.3 using two different servers

Settings in master server: max_wal_senders = 1 wal_level = 'archive' archive_mode = on archive_command = 'cd .' wal_keep_segments = 10000 Settings in slave server: in recovery.conf file: ...
1
vote
1answer
32 views

Postgresql pgAdmin, count(*), max(pk_field) giving inconsistent results

I am running postgresql 9.3.4 on a Centos 6.5 server as a back end for a web app which I am currently load testing. Presently, if I visit the state table in pgAdmin, right click on "count", I get a ...
0
votes
0answers
29 views

Having trouble configuring phpPgAdmin with PostgreSQL 9.3 on CentOS 6

Trying to hook up phpPgAdmin on my virtual box and having some issues with pg_hba.conf. I added the IP I SSH through, connecting through a VPN, in IPv4 and changed all the methods to md5. I then go to ...
0
votes
0answers
18 views

Is it possible to access a linked server in SQL Server 2008 R2 from a Postgres Foreign Data Wrapper?

Now I know this is a looong shot...but it appears to me that it might be possible to access my dumpy, legacy, FoxPro, Free Table directory, based database from Postgresql by: Setting up an ODBC ...
2
votes
1answer
80 views

Select data based on records created_at column

I have two tables Keywords, ProjectReports: http://sqlfiddle.com/#!15/06ae3/1 As you can see in that example everything works fine, but I want to get more data from those 2 tables and I don't know ...
4
votes
0answers
44 views

Investigating rollbacks in Postgres

A Postgres monitoring script, check_postgres.pl, is warning me that a large number of rollbacks are occurring on a database server. How can I investigate what queries are being rolled back? I have ...
0
votes
0answers
37 views

huge size of /var/lib/postgresql/9.3/main/base/1 directory (postgresql)

i'm using postgresql 9.3.4 and real size of database is about 5.5 GB but POSTGRESQL fill disk about 256 GB !! is this POSTGRESQL Cache and what is method to clean thing issue ?
1
vote
1answer
38 views

How to set bytea_output to hex?

I cannot seem to change bytea_output to hex. I have tried this method: ALTER DATABASE dbname SET bytea_output TO 'hex'; I have tried to edit postgresql.conf bytea_output = 'hex' and restarted ...
0
votes
1answer
34 views

changed password for postgres login in pgAdmin, now can't connect to server

I was trying to use pg_dump to script out a database and in the process I used pgAdmin to change the password for the postgres user. I realize (now) that changing that password was a bad idea. But now ...
3
votes
0answers
55 views

PostgreSQL custom operator for equality

I want to build a custom equality operator in PostgreSQL, which can be used in GROUP BY, UNION and DISTINCT [ON] for the json type (just for the sake of curiosity, not for real-world implementation -- ...
1
vote
0answers
27 views

Transfer an Advantage Database's Schema/Data to a PostgreSQL Platform Using C# Program

I am currently in the process of writing a .NET program with C# to transfer a database on a Sybase/Advantage 9.1 platform to a PostgreSQL 9.3.4 platform. I am doing this transfer in two stages - ...
0
votes
1answer
51 views

Issue working with Postgres on Windows

I am running PostgreSQL 9.3 on my machine. I have PhpPgAdmin running on a cloud, using which I exported the database I am supposed to be working on, selecting "Structure and Data" and "Download". ...
1
vote
1answer
39 views

Postgres resource management

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
1answer
33 views

Postgresql Function Trying to Insert into 1 row instead of 2

Postgresql 9.3, Mac OS X 10.6.7 I created this function and I would like to insert both results into the same row. Obviously the way I have it inserts into 2 rows. First, here is the Table T1: ...
2
votes
1answer
65 views

Extremely long query time after upgrading to Postgres 9.3 from 9.1

We're attempting to upgrade from Postgres 9.1 to Postgres 9.3 on a FreeBSD 9.2 zfs box. Performance is noticeably slower across the board, but we have one query that has gone from 353ms to >200000ms. ...
1
vote
2answers
72 views

Return several values from a PL/pgSQL procedure - to speed up the query

I have a non-forking game daemon written in Perl, which uses acync queries to write player stats into a PostgreSQL 9.3 database. But when I need to read something from database (like if a player is ...
1
vote
1answer
62 views

Add NOT NULL constraint to large table without table scan

Trying to add a NOT NULL constraint to a table with 1 billion rows. I cannot afford a table lock for more than a couple of seconds. Is there a way to prevent a full table scan during the alter table ...
0
votes
0answers
69 views

High Availability in PostgreSQL with automated failover

I am currently working on designing an architecture for High Availability in PostgreSQL for a database of approximately 5TB in size. Also, I would like to re-direct my reads to a slave node(preferably ...
2
votes
1answer
30 views

How can I know whether postgres is currently running synchronous or asynchronous replication?

I see from ps that: postgres: wal sender process replicator X.X.X.X(38681) streaming 0/17AF5438 But is there any way for me to know whether it is synchronous or asynchronous? I made the relevant ...
3
votes
1answer
56 views

How can I achieve clustering of rows without the exclusive lock and logging overhead of the `cluster` command?

The CLUSTER command on a big table can take a long time, and blocks both reads and writes to the table while it runs. I don't need the data in my table to be strictly sorted in index order, I just ...
0
votes
0answers
28 views

pgAdmin Slony Creation Scripts

I have installed pgAdmin 1.18.1 but whenever I am trying to create a new cluster using Slony-I, it is always saying that it is missing some scripts. I have copied some .sql scripts from my Slony ...
0
votes
1answer
32 views

Easy way to replace an YAML array of IDs with strings from a joined table

I have a database full of camp data. Each camp has many campers and has many camp_weeks. In the table of campers, there is a YAML encoded array of IDs for the camper's camp weeks. The project ...
2
votes
1answer
49 views

Does postgres efficiently re-use space from deleted rows?

From the docs: In PostgreSQL, an UPDATE or DELETE of a row does not immediately remove the old version of the row [...] But eventually, an outdated or deleted row version is no longer of interest ...
1
vote
1answer
34 views

RANGE clause error within window function

I'm trying to use postgres' RANGE clause within a window function to dynamically set the window to the past 4 weeks not including the current date. Here is my attempt at it: SELECT ...
2
votes
1answer
105 views

Using PgBarman for PostgreSQL 9.3 backup

I'm setting up a backup strategy for a PostgreSQL 9.3 db. My job includes: take db backups every day/week/month use a Synology NAS as remote backup device I'm thinking of using Postgres ...
1
vote
1answer
72 views

Decrease WAL file size for backup

I'm setting up a backup strategy based on WAL files archiving and transmission via rsync. As DB activity will propably be very low (less than 100 inserts/updates/deletes per day), at least in the ...
3
votes
1answer
118 views

How do I decompose ctid into page and row numbers?

Each row in a table has a system column ctid of type tid that represents the physical location of the row: begin; create table t(id serial); insert into t default values; insert into t default ...
3
votes
1answer
56 views

Can Postgres synchronous replication stall a cluster if secondary/standby crashes?

In a setup with Postgres synchronous replication, let's say one of the secondary/standby machines crashes, will that lock/halt the master? That is, since the Postgres master always waits for ...
1
vote
0answers
45 views

appending 2 tables based on common columns in Postgresql 9.3

I have 2 large tables that I need to combine into a single table--for statistical analysis. The two tables have some variations in column named, otherwise I would just use an INSERT statement. So I ...
7
votes
1answer
82 views

How do data checksums interact with streaming replication?

Data checksums are a new feature introduced in 9.3, and: there is a new GUC parameter "ignore_checksum_failure" which will force PostgreSQL to continue processing a transaction even if corruption ...
0
votes
2answers
34 views

Get distinct results based on column and satisfy condition

I have the below select which is selecting from a table with the columns: id, project_id, keyword_id, position, Data example: i, p_id, k_id, p 1, 1, 1, 4 2, 1, 1, 5 3, 1, 1, 6 5, ...
0
votes
0answers
38 views

COPY TO .csv and add quotes to header in PostgreSQL

I use the following in the command line \copy (select id, pid from table1) to '/path/to/file.csv' with delimiter ';' CSV HEADER; Everything works fine. But I want the header's string to be quoted ...
0
votes
1answer
84 views

Postgres live backup

Goal: Maintaining a constant live backup of a postgres cluster. In case of cluster failure, backup/Secondary will contain most up to date view of Primary cluster. This is not a hot standby ...
0
votes
2answers
62 views

PostgreSQL 9.3 - standard_confirming_strings option in postgresql.conf not affecting remote machine

I am updating a program at work that communicates with a postgreSQL 9.3 database. I need to turn off an option in the postgresql.conf file called standard_conforming_strings, so that I can store ...
2
votes
1answer
61 views

Is this inconsistent treatment of `void` return from sql and plpgsql functions documented?

begin; create table foo(id integer); create function f1() returns void language sql as $$delete from foo;$$; create function f2() returns void language plpgsql as $$begin delete from foo; end;$$; ...
0
votes
1answer
24 views

pg_basebackup: could not set compression level -1

When trying to run pg_basebackup I received the following error message: pg_basebackup: could not set compression level -1 (null) using the -z option. I received a similar error message when trying ...
0
votes
0answers
105 views

how to append or update JSON datatype value in postgreSQL

hey guys i'm using PHP's codeigniter to create my application and postgreSQL as database and i have to update some value in database. So, let's say we have table structure like this:- CREATE TABLE ...
5
votes
2answers
125 views

Indexing a large static dataset

My current situation is this: I have 328 million lines of data in a flat csv file. Not optimal. I want to be able to query this data (I'll explain in more detail). Grep is getting tired. This data is ...
8
votes
1answer
255 views

How do I 'hint' the cardinality of a recursive CTE?

I'm using the following recursive CTE as a minimal example, but in general, the optimizer has to use default 'guessed' cardinalities for recursive CTEs: with recursive w(n) as ( select 1 union all ...
1
vote
0answers
32 views

pgpool stability issues

I am using pgpool-II 3.3.1 to load balance connections to my postgres 9.3. I am experiencing an ocassional stability issue where clients are not able to connect to pgpool (but they are able to connect ...
1
vote
0answers
163 views

Setting password for 9.3 PostgreSQL server

I currently have a 9.3 server running (on Ubuntu 14.04) with "trust" set as the authentification method in pg_hba.conf. How can I set the password again. In pgadmin the server currently doesn't allow ...
2
votes
1answer
38 views

How can I enable logging for a single function in PostgreSQL?

I want to log the whole function runtime for a single particular function in PostgreSQL? Is this possible? The value of log_min_duration_statement = 3000 in postgresql.conf and I've tried to do: ...
0
votes
1answer
445 views

Authentification failure for PostgreSQL server (9.3)

I can currently not connect to my Postgresql server (9.3) from pgadmin (Running on Ubuntu 14.04). The server according to pg_lsclusters is on port 5433. Ver Cluster Port Status Owner Data ...
2
votes
2answers
133 views

postgreSQL 9.3 streaming replication delay

I'm running a fairly simple WAL streaming replication setup between two postgreSQL 9.3 servers that are on a private 1Gb LAN, but recently I've noticed that there is an increasing amount of ...
1
vote
1answer
92 views

Upgrading using pg_upgrade on windows fails

When upgrading from postgres 9.2 to 9.3 by following the upgrade procedure found in PostgreSQL 9.3.4 documentation about pg_upgrade, the following command fails: RUNAS /USER:postgres "CMD.EXE" ...
1
vote
1answer
42 views

Postgresql reset_xlog

I just mistakenly moved the pg_xlog folder of a live, running postgres server. When I restarted the server shortly after, it failed to start because of a WAL problem (obviously). For non-technical ...