PostgreSQL 9.3 : 2013 release of PostgreSQL

learn more… | top users | synonyms

0
votes
1answer
20 views

Bulk insert in multiple tables

I have two tables on which I perform some bulk inserts: key: key_id(pk), key_name related_key: key_id(fk), related_key_id First Insert: values_data = "($$key_1$$), ($$key_2$$)" INSERT INTO key ...
0
votes
0answers
28 views

Storing DB credentials in an application source code?

I am making an app which connects to a DB, and the app will have plenty different userslogins. Is it a good design choice to have a specific DB login/password for the application, using which the ...
0
votes
0answers
5 views

PostgreSQL: Which connections is blocking ALTER TABLE?

I would like to add some fields to a table in all testing and production DBs, all of which are stored in PostgreSQL 9.3 on Amazon RDS. ALTER TABLE users DROP COLUMN IF EXISTS device_language; ALTER ...
0
votes
1answer
24 views

Useful guide & FAQ for Application+SQL [on hold]

Of course when you are making your first application for fun it doesn't matter that much how you deal with things. But when you need everything to be up to the current standards you don't always know ...
2
votes
2answers
25 views

Compare multiple field to single IN query

I have a situation where I need to compare 3 different fields of a same table to a single IN query, eg: select unit from trips where owner1,owner2,owner3 in (select id from login) Can this be done ...
1
vote
2answers
23 views

How to tell which indexes are not being used

Postgresql 9.3 Debian 7 I have a lot of huge indexes in a legacy database I'm trying to optimize. Thinking about dropping all the useless ones, but how can I tell how often they are used and if they ...
-1
votes
0answers
22 views

Can't log into psql with correct password [closed]

I just uninstalled and reinstalled PostgreSQL 9.3 on my Windows 8 PC, after I forgot my password and none of the password reset solutions were working for me. During the installation, I set the ...
2
votes
2answers
78 views

GROUP BY one column, while sorting by another in PostgreSQL

How can I GROUP BY one column, while sorting only by another. I'm trying to do the following: SELECT dbId,retreivalTime FROM FileItems WHERE sourceSite='something' GROUP BY seriesName ...
2
votes
1answer
16 views

pg_upgrade and tablespaces (postgres 9.2 to 9.3)

I have a 9.2 installed with tablespaces sitting at: /var/lib/postgresql/9.2/main and /opt/postgres/9.2/second And unfortunately they are on different filesystems. This is a Debian package ...
1
vote
1answer
22 views

PGAdmin III Server Status Error: Could not read directory “pg_log” No such file or directory

Running PostgreSQL 9.3 on Ubuntu 14.04 and pgAdmin III 1.18.1 on Windows 7. I tried Tools->Server Status and I get an error: "Could not read directory "pg_log" No such file or directory" I ...
1
vote
1answer
9 views

Create user to monitor database activity on Postgres

Postgres 9.3 Debian 7 I want to create a monitor user called monitor that the only permission is to run queries to monitor all the database activity. I created the user and it can select from ...
0
votes
1answer
10 views

remote postgresql 9.3 login using pgadmin3 and a public network server to connect to LAN-only machine

I want to connect to a machine running postgresql 9.3. The machine itself is not reachable via a direct IP but is in a LAN with another machine with a public IP address. My current approach is: ...
1
vote
1answer
43 views

Update different columns on different clauses

I have a database table in Postgres 9.3 with the following layout: id SERIAL, col1 INT, col2 INT Whenever a new row is inserted, I will have to update ALL rows col1 and/or col2 in different cases ...
1
vote
1answer
15 views

Grant usage partially on schema to user on Postgres

I granted INSERT in a specific table for one user. The problem is that I need to also grant USAGE in the schema for this same user, but granting USAGE in schema also turn all relations in that schema ...
1
vote
1answer
35 views

PostgreSQL Slave has more files in pg_xlog than /wal_archive

Background: for various reasons I am switching PostgreSQL Slave from a Streaming replication to a warm standby replication. The streaming replication was setup using this guide: ...
0
votes
2answers
70 views

How can I make this query more efficient?

This is the query: SELECT races.*, tmptimers.last_start_time, tmplaps.updated_at AS last_updated_at FROM races LEFT JOIN (SELECT * FROM timers WHERE timers.user_id = 1) AS tmptimers ON ...
1
vote
1answer
116 views

What does exec(text) do?

I just found the following function in two RDS PostgreSQL 9.3 DBs: -- -- Name: exec(text); Type: FUNCTION; Schema: public; Owner: adam -- CREATE FUNCTION exec(text) RETURNS text LANGUAGE plpgsql ...
3
votes
1answer
33 views

PostgreSQL: Trigger for updating view doesn't work

I'm trying to make the trigger for updating a view work. If I'm doing and INSERT it works! But when I'm trying to do an UPDATE it just doesn't run. CREATE OR REPLACE FUNCTION test_trigger_function() ...
0
votes
1answer
6 views

PostgreSQL 9.3: pg_dump fails due to tiger.direction_lookup

I am trying to dump the schema from one of my PostgreSQL 9.3+PostGIS databases: /usr/lib/postgresql/9.3/bin/pg_dump --schema-only -h stg-db.....rds.amazonaws.com ...
0
votes
1answer
18 views

Unmount/detach/deactivate PostgreSQL database

I manage a server that serves several web applications that use PostgreSQL as a data store. Each web application has a separate role and database, and each database is stored exclusively on a ...
0
votes
1answer
21 views

PostgreSQL streaming versus file-based replication (In terms of server behavior & configuration)

I am trying to understand best uses of PostgreSQL replication and how it works so I can troubleshoot in a production environment. I am having a hard time understanding the differences between these 2 ...
0
votes
1answer
17 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
34 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
153 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 : +----------------------------+ +--------------------------+ | ...
2
votes
1answer
28 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
42 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
23 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
25 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
62 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
35 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
93 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 ...
1
vote
1answer
42 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
38 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
70 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
56 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
30 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
78 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
126 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
46 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
82 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
47 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
99 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
1answer
58 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
65 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
98 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
64 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
78 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
47 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
58 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
41 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 ...