PostgreSQL is a powerful, enterprise class, open source RDBMS. It has more than 15 years of active development and a proven architecture that has earned it a strong reputation for reliability and data integrity. It runs on all major operating systems, including Linux, UNIX and Windows. It prides ...

learn more… | top users | synonyms (1)

0
votes
2answers
2k views

Can't backup a Postgres database: FATAL: Peer authentication failed for user “postgres”

I can connect to my database no problem remotely, either from PHP scripts on our webserver or using PGAdmin3. Unfortunately, when I try to run pg_dump backups locally on the server itself, I get: ...
2
votes
1answer
303 views

Why Django session table grows on PostgreSQL

I use PostgreSQL 9.1.6 via PgBouncer 1.4.2 in my Django 1.4.8 project. django_session table grows all the time. VACUUM does not help. VACUUM FULL runs for hours. Only TRUNCATE helps usually. The ...
0
votes
1answer
23 views

Can a connection's prepared statements be altered by Postgres's admin functionality?

I am trying to tamper proof as best as possible a peer to peer application. I am concerned that the prepared statements will be altered. Can the Postgres admin alter a connection's prepared ...
0
votes
1answer
428 views

multi-master to single-slave replication at table level with PostgreSQL or MySQL

Here is my scenario Master1 hosting DB1 Master2 hosting DB2 ... MasterN hosting DBN replicate to: Slave1 hosting DB1,DB2... DBN I've read similar questions and they recommend to start different ...
12
votes
1answer
7k views

Create index if it does not exist

I am working on a function that allows me to add an index if it does not exist. I am running into the problem that I cannot get a list of indexes to compare to. Any thoughts? This is a similar issue ...
0
votes
0answers
22 views

Postgres Order By [on hold]

I am attempting to run a Query to sort by one of our columns study.version. However every time I run this query, I get hit with a syntax error and can't seem to find out why this would be wrong: ...
0
votes
1answer
44 views

postgres explain plan with giant gaps between operations

I have a postgresql explain plan that seems to have large gaps between the join, sort & filter processes and can't explain (sorry) it. The query is taking an inordinate amount of time and the ...
1
vote
1answer
31 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 ...
2
votes
1answer
39 views

PL/pgSQL function or rCTE to detect depth of relations between two tables

I need to perform a PostgreSQL Function for checking the depth of relations. There is a circular binding from table a to b, back to another element in a and, in some cases back again to b. These ...
1
vote
1answer
91 views

PostgreSQL's Query Hierarchical data poor performance

Badly need some technical assistance on this query performance issue I'm working on. Summary of the data structure is: 1 project has many homeprofiles, 1 homeprofile has many tasks, 1 task has many ...
0
votes
0answers
18 views

Trying to set up automatic process to append and update tuples [on hold]

I am trying to figure out a way to automate a process that I currently have that appends a new column on and updates two or three other columns when I add a new table into a database. The goal is to ...
3
votes
1answer
66 views

For alignment optimized table is bigger than original table - why?

In another question I learned that I should optimize the layout from one of my tables to save space and have better performance. I did this, but ended up with a larger table than before and ...
1
vote
2answers
6k views

Connect to Postgresql 9.2 from Eclipse Data Tools Platform

GOAL: My goal is to write sql code within Eclipse to query a Postgresql database. The reason I want to write my queries within Eclipse is to then write R or Python code to perform statistical analysis ...
0
votes
0answers
10 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 ...
0
votes
1answer
63 views

Automatically rollback two-phase transactions on session disconnect

I'm using two-phase commit in Postgres to reliably work with an external system (Amazon EC2). I.e. I first do a PREPARE call, then call the external system. If the external system call fails for some ...
0
votes
0answers
34 views

Select script for selecting two columns in a table [migrated]

I need help writing a Select script which selects data from two columns based on a condition. Say I have a table Table: X Columns: ID, Type , and Old_type Column "Old_type" has nulls or historic ...
0
votes
2answers
19 views

starting pgbouncer on startup

I installed pgbouncer and configured it and it starts only via using this command pgbouncer /etc/pgbouncer/pgbouncer.ini however using service pgbouncer start it dose not start. I then set up ...
1
vote
1answer
38 views

Good layout of 3d point data for spatial queries in Postgres?

Like shown in another question, I deal with a lot (>10,000,000) entries of points in a 3D space. These points are defined like this: CREATE TYPE float3d AS ( x real, y real, z real); If I am ...
1
vote
1answer
44 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" = ...
3
votes
3answers
491 views

Full Text Search With PostgreSQL

i have a table with this rows: Stickers ------------------------------------------------------ ID | Title |Keywords (ts_vector) ------------------------------------------------------ ...
1
vote
1answer
12 views

Is 'internallength' always useful in custom type definition in Postgres?

In our database we use a custom type to store 3D locations: CREATE TYPE float3d AS ( x real, y real, z real); In many examples I see INTERNALLENGTH used as a parameter with the definition of ...
4
votes
2answers
1k views

PostgreSQL query with max and min date plus associated id per row

I have the following table: CREATE TABLE trans ( id SERIAL PRIMARY KEY, trans_date date, trans_time time ); I want to have the following view CREATE OR REPLACE VIEW daily_trans ...
3
votes
3answers
247 views

Checking whether two tables have identical content in PostgreSQL

This has already been asked on Stack Overflow, but only for MySQL. I'm using PostgreSQL. Unfortunately (and surprisingly) PostgreSQL does not seem to have something like CHECKSUM table. A PostgreSQL ...
2
votes
1answer
15 views

Replicate single table from Postgres 9.3 replica

I have an existing Postgres 9.3 server acting as a read-only replica using the built-in streaming replication. I need to replicate a single table from a database on that server to another physical ...
5
votes
2answers
1k views

Postgres full text search with multiple columns, why concat in index and not at runtime?

I've come across full text search in postgres in the last few days, and I am a little confused about indexing when searching across multiple columns. The postgres docs talk about creating a ts_vector ...
6
votes
2answers
72 views

Index that is not used, yet influences query

I've got a PostgreSQL 9.3 table with some numbers and some additional data: CREATE TABLE mytable ( myid BIGINT, somedata BYTEA ) This table currently has about 10M records and takes 1GB of ...
3
votes
1answer
75 views

PostgreSQL + query planner + amount of entities

I'm working on college work about how query planner uses statistics and makes the most optimal query. I have read the 57.1. Row Estimation Examples article and I know how's the PostgreSQL calculates ...
0
votes
0answers
27 views

Import xml data into postgresql table [on hold]

I've got xml file with data : <teryt> <catalog name="TERC" type="all" date="2014-01-01"> <row> <col name="WOJ">02</col> <col name="POW"/> <col name="GMI"/> ...
0
votes
1answer
101 views

WAL archiving from a standby backup strategy Postgresql 9.3

I am trying to figure out if wal archiving from a standby is possible and a viable option for a backup strategy on Postgresql 9.3. The idea is to be able to take regular base backups and wal ...
1
vote
1answer
32 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 ...
0
votes
2answers
22 views

Filling in missing dates in record set from generate_series()

Consider: with days as (select day::date from generate_series(date '2013-01-01', date '2013-01-01' + 365, interval '1 day' day) day ) select 'Inspections'::text as data_label, count(i.reporting_id) ...
0
votes
2answers
51 views

Database Design Help: MySql/PostgreSQL Single Database/Multiple Databases

We are planning to build a web based custom business analytics tool using PHP and MySQL/PostgreSQL. In our application, we are planning to upload customer’s data using a Excel sheet and these data ...
3
votes
1answer
29 views

duplicate key violates unique constraint when using multi-threading

I am using ExecutorService with a fixed thread pool of 50, and a fixed database connection pool of 50, using HikariCP. Each worker thread processes a packet (a "report"), checks if it is valid (where ...
6
votes
4answers
8k views

tuning postgresql for large amounts of ram

I have two identical servers (in terms of hardware), they are both standard installations of windows server 2008 r2, with minimal software installed (basically my code and required stuff like jvm ...
0
votes
1answer
172 views

Sync offline PostgreSQL server

I am new to PostgreSQL. I am looking for a solution wherein 2 mirror images of a database sit on a local and remote server. All the work is done on the local server and at periodically synced with the ...
0
votes
2answers
31 views

Auto increment char varying column prefixed with some characters

I have a table in PostgreSQL for hospital patients and I want to make a primary key for it as PAT0000001 (char varying) and to keep that field as auto-increment as well. Is there a way to achieve ...
0
votes
0answers
17 views

How to make inet_server_addr() return localhost in spite of ::1/128

How to make inet_server_addr() return an name as IPv4?
-1
votes
1answer
39 views

Database Design for classifieds site [on hold]

I'm creating a classifieds site where there are many categories and subcategories. 1) Each category has different fields. 2) Each listing has one or more images I need help with the database design. ...
4
votes
3answers
5k views

PostgreSQL user can not connect to server after changing password

I've met this with 4 roles I created: After changing password for a user in pgAdmin III using the GUI (1), that user can not log in any more. pgAdmin III show error message: An error has occurred: ...
0
votes
2answers
54 views

How to change schema so that account_id reference is unique among 3 tables

Original image: Updated to have more-correct terminology and an 'is_debit' column: I am designing a (PostgreSQL) schema for a lottery website that uses double-entry accounting. 'jackpots', ...
1
vote
2answers
56 views

How to construct a set-based query for subset problem?

I have a DB of the following structure (picture shows simplified version) How should I construct a query to get only those recipees, that can be cooked, i.e. for each ingredient in a receipe ...
10
votes
2answers
620 views

Modelling constraints on subset aggregates?

This is a cross-post from Stack Overflow. I figured I might get more interesting thoughts here. I am using PostgreSQL but I figure most of the top-end db's must have some similar capabilities, and ...
0
votes
2answers
1k views

PostgreSQL cannot start after replacing contents inside its Data folder

When recovering the Postgresql database from a hacked Ubuntu 12.04 server, I copied the data directory to another location, reinstalled the OS and PostgreSQL, stopped PostgreSQL service, removed the ...
0
votes
0answers
32 views

Wrapper function with SECURITY DEFINER is slow

I have a strange behavior of wrapper function with SECURITY DEFINER option. Function 1 - Queries data what is not directly accessible by user. Function 2 - Is a wrapper that calls function 1 with no ...
2
votes
1answer
36 views

Why is my composite index not used for this table?

I am working with a fairly complicated schema, but I've simplified it for the purposes of this question. Table "public.scheduler_appointment" Column | Type ...
0
votes
1answer
18 views

Custom personal monitor sql by writing the .psqlrc file

I want to custom my personaly monitor sql by writing the .psqlrc file , but I encounter some problems . --tablespace skypcsuit=> \db List of tablespaces Name ...
0
votes
1answer
18 views

PostgreSQL - If I run multiple queries concurrently, under what circumstances would I see a speedup? Under what circumstances would I see a slowdown?

I approach you all humbly as one who is NOT a DBA, and I'm sure that my question is fraught with conceptual shortcomings and "it depends on" land mines. I'm also pretty sure that all of you who ...
0
votes
1answer
149 views

Taking hot backup of slave node in postgres (master-slave config with repmgr)

I have a postgresql setup with master-slave configuration using repmgr. I'm struggling with taking a consistent snapshot backup (pg_start_backup) of the slave node, this is due the fact that the slave ...
1
vote
0answers
23 views

postgres - generating documents for full text search taking forever and eating up disk space

We use PostgreSQL and Django-Haystack with Elasticsearch for search on our service, and were thinking of switching to Postgres's built in full text search because of various annoyances using a NoSQL ...
1
vote
0answers
39 views

Should one always VACUUM ANALYZE before REINDEXing in PostgreSQL 8.4?

Early in the morning every day a pgAgent job refreshes the contents of table A from table B on my PostgreSQL 8.4 database. Table A contains around 140k records across 91 columns and has two indexes - ...