PostgreSQL 9.3 : 2013 release of PostgreSQL
0
votes
1answer
14 views
Posgresql 9.3: Show domain details
Showing the columns and constrains of a table in Posgresql is done with:
=> \d+ <table_name>
Which lists the columns, data types and modifiers for a table.
How can I show the details and ...
3
votes
1answer
22 views
Why does the behavior of array syntax differ from '(?,?)' syntax when updating a point field and that field is NULL?
I'm using PostgreSQL 9.3.5.
Suppose I have the following table:
CREATE TEMPORARY TABLE point_test ("name" varchar(255), "pt" point);
I then insert a row, leaving pt NULL:
INSERT INTO point_test ...
2
votes
2answers
125 views
SELECT UNION from two views of the same table
(Sorry the the vague title, if anyone want to re-edit, be my guest!)
I have these tables and views for a factory inventory module :
+----------------------------+ +--------------------------+
| ...
1
vote
1answer
20 views
What's the data flow between Pgpool, Postgresql and client
I'm new in Pgpool.
I want to ask the question, what is the dataflow between Pgpool, Postgresql when client querying data
1/
Client query data to Pgpool,
PgPool ask Postgresql
Postgresql ...
4
votes
0answers
33 views
How to handle bad query plan caused by exact equality on range type?
I'm performing an update where I require an exact equality on a tstzrange variable. ~1M rows are modified, and the query takes ~13 minutes. The result of EXPLAIN ANALYZE can be seen here, and the ...
1
vote
0answers
21 views
Postgres Performance Over Group by with MAX and MIN
I just want to retrieve the data for specific location of customers with their ever first and last purchases made in the system.
Table details :
CREATE TABLE customer_location ( id UUID, location_id ...
0
votes
0answers
22 views
Bucardo status does not feel if the slave is down
I am using bucardo as third party to postgresql replication between two sites. everything ok except buacrdo status <sync_name>.
I have a Master-slave sync on master server. when I poweroff the ...
3
votes
2answers
42 views
Pagination with PostgreSQL 9.3: counting number of pages
I'm implementing pagination and sorting of rows in, let's say, products table, using multicolumn index on category, score and id.
-- index
create index category_score_id on products(category, score, ...
1
vote
1answer
31 views
Restrict two specific column values from existing at the same time
I have a PostgreSQL example table where at most one row that is not of type 'c' should be allowed.
I would appreciate any help creating a constraint that will enforce this.
CREATE TABLE example
(
...
1
vote
1answer
72 views
PostgreSQL 9.3 - Performance Issue: Counting table entries slower with jdbc postgres driver
I'm using PostgreSQL 9.3 and want to ask if it is possible to improve the performance of my queries based on the following database schema:
CREATE TABLE "entities" (
"id" BIGSERIAL,
"type" INT ...
0
votes
1answer
22 views
Problems installing Postgis extension on Ubuntu 14.04
I have Ubuntu 14.04 with Postgres 9.3 and I'm trying to install Postgis extension. One of the most recommended and straightforward ways I found is very simple:
sudo apt-get install postgresql-contrib ...
1
vote
1answer
35 views
What is the impact of building Postgres with --disable-thread-safety option?
I am a newbie in the database field. Recently I have been trying to install Postgres 9.3.4 on AIX 6.1. When I was building the code with the configure command, I ran into this error:
checking thread ...
1
vote
1answer
40 views
Missing the pg_ctl package in Postgres 9.3 installation
I have installed postgresql 9.3 on ubuntu 14.04 using "apt-get install postgresql".
Everything was going well until I discovered that I do not have access to the "pg_ctl" commands. Installing the ...
1
vote
1answer
51 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" = ...
2
votes
1answer
27 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
61 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
75 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
37 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
53 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
28 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
88 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
50 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
53 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
63 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
46 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
68 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
38 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
55 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
40 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
35 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
74 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
77 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
67 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
71 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
31 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
58 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
31 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
35 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
52 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
43 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
128 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
91 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
140 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
70 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
48 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
84 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
36 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
42 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
91 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
70 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 ...
