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)

0
votes
1answer
513 views

Close all connections to postgres without having a connection

I have a cron job that runs a script that connects to a remote postgres database that I do not administer. We are allotted a single connection and, on the occasion that I or a colleague log in to run ...
3
votes
1answer
1k views

Debugging why postgres is snowballing with system cpu usage

I am having an issue where my postgres database which run on a dedicated machine is getting hammered with kernal level cpu usage. This essentially takes down the web service that run on this database. ...
1
vote
1answer
210 views

complex update query postgres

I've got a bunch of ids from table messages and I want to update the table activities. This is how I imagined my query would work (so you get better idea what I'm trying to accomplish): update ...
1
vote
2answers
457 views

Extract all tags from hstore field

I need to extract (copy) all hstore tags by object id from a postgres 9.2+postgis 2.1 database into an external file for post-processing. Since the keys and values vary per record, and I don't know ...
1
vote
1answer
1k views

Secure postgresql database encryption

According to this answer, it's not possible to have a fully secure database encryption with pgcrypto. Now, 2 years later (in postgresql 9.3) is there any new extension or new method to have a ...
4
votes
2answers
617 views

Order of columns in a compound index in PostgreSQL (and query order)

I have a table with 50K rows. It is actually a PostGIS table. The query has 4 parts (1 mandatory) (3 Optional) intersection box (a geography rectangle) with 4 lat,long (I use st_intersects) ...
3
votes
2answers
2k views

Streaming Replication Log is puzzling me

I have a streaming replication hot-standby setup, which seems to be running all good. I can login, execute selects and the result seems to be up-to-date with the master. However in the log of the ...
0
votes
0answers
252 views

Postgres vs MySQL Database Maintenance

I have been using Postgres and MySQL for a long time now. There are a few routine maintenance chores that must be performed on a regular basis to keep a PostgreSQL server running smoothly. As as ...
2
votes
2answers
357 views

Postgres point() index not being used

\d aggregate Materialized view "public.aggregate" Column | Type | Modifiers ------------------+-----------------------------+----------- ...
3
votes
0answers
390 views

Poor performance in my pgpool cluster

I've configured a postgres cluster with pgpool2 using the following link. The replication seems to be ok, but I ran some benchmarks (pg_bench) and the performance is lower than a single node, for ...
1
vote
1answer
75 views

How can I improve performance of update query in PostgreSQL

I have a table with about 80 million records. I have added a new attribute (heigth_category) which I want to fill based on another attribute (heigth). If the heigth is between 0 and 6, I want to fill ...
0
votes
1answer
371 views

PostgreSQL - Return unique combinations of columns based on where clause

This is quite a long question, please bear with me. So I'd like to first explain I have a database of firewall logs created using the following command: CREATE TABLE firewall_logs_mapped ( ...
3
votes
1answer
275 views

PostgreSQL - VACUUM FULL does not free space back to the OS

On a Postgres 9.1 installation, I'm trying to reduce the amount of Postgres' disk usage by dropping records from tables and then running VACUUM FULL on those tables. My understanding of VACUUM FULL is ...
1
vote
1answer
328 views

Logging Postgres startup errors on Windows

I have been experimenting with Postgres configuration parameters and noticed that when I use settings that prevent Postgres from starting nothing shows up in the pg_log directory logs. I have ...
1
vote
2answers
77 views

Nearest value for each foreign key

I have a table with a primary key, timestamp, value, foreign key. I would like to find the nearest value to a timestamp for each of the foreign keys in a list. Unfortunately, the gap from the given ...
3
votes
1answer
972 views

What PL/pgSQL debugger can I use?

I'm building a lot of PL/pgSQL functions and a debugger would really come in handy because RAISE NOTICE is not enough. I read about a pgAdmin plugin you can install but it was only on Windows and I'm ...
0
votes
0answers
91 views

Postgres 9.2 on Windows

Simple question, but utterly infuriating. Trying to use psql from a command prompt, but can't access any of the previous commands with the up/down cursors. I could also really do with finding out ...
3
votes
1answer
211 views

Postgresql query slowed with table growth

I have a very simple table in Postgresql, and need some guidance to speed up my queries. Recent large expansion of the data in the table has made everything grind to halt. Table body_feature: ...
3
votes
1answer
1k views

PostgreSQL Streaming Replication - Slave remains in recovery mode

PostgesSQL 9.3.x I cannot run some commands on slave because: "ERROR: recovery is in progress" I set up streaming without archive. Initialized with pg_basebackup. Dataset has about 180GB . This is ...
3
votes
1answer
182 views

Postgresql 9.3 replication: Preventing wal-files from building up, if receiver goes offline

I have a streaming replication running between two servers. I just upgraded to postgresql 9.3 from 9.2. Earlier on in my 9.2 setup, I have tried that my slave server went offline due to a network ...
2
votes
1answer
288 views

Pg_upgrade failed - what does “re-initdb” mean?

My pg_upgrade command failed. During the upgrade it told me, that if things were failing after a curtain point (which they did) Then I should: If pg_upgrade fails after this point, you must re-initdb ...
1
vote
1answer
786 views

Postgres, levenshtein distance and nested queries

I have a DB Table with 3,000,000 rows or more like that: |ID|T4|T16|T64|TL where ID =INTEGER (PK) and T4,T16,T64,TL are text fields. Each one is larger in length than the previous, i.e., LENGTH(T4) ...
4
votes
1answer
2k views

pgBouncer works great but occasionally becomes unavailable

I'm running pgBouncer in front of a busy postgres 9 database. For most of the time it works fine. But every few hours I'll get an error email from my application with an exception from psycopg2: ...
1
vote
1answer
102 views

Postgres settings reported differently in pgsql

We are running PostgreSQL 9.2 on CentOS 6 and I am seeing the settings are reported differently when I run the show effective_cache_size; command vs select * from pg_settings where name = ...
2
votes
1answer
321 views

PostgreSQL 9.2; Altering a table to use another sequence

I've created a table like so: create table doki_data_item2 (like doki_data_item including defaults); but this new table uses the same sequence as the old table. So two questions: How can I copy ...
0
votes
1answer
294 views

Postgres EXPLAIN ANALYZE behavior

I want to benchmark performance of a particular complicated query JOIN for various values of primary key ID. Most previous approaches say to use EXPLAIN ANALYZE for the query in question and get the ...
1
vote
2answers
66 views

SQL Select query with postgresql

I'd like to find all rows if is shows even once a particular value with postgresql For example : id TIT_CODE CLI_NOM CLI_PRENOM CLI_ENSEIGNE 1 M. DUPONT Alain A 2 M. ...
0
votes
1answer
137 views

Choosing between single table and multiple table database scheme [closed]

I'm building an application which saves addresses in a PostgreSLQ 9.3/PostGIS 2.1.1 database. Currently I am trying to find an efficient database schema, so far I've come up with two that would ...
1
vote
1answer
902 views

Streaming replication between postgresql 9.2 and 9.3

Does anyone know if it's problematic to have a postgresql 9.3 slave setup to do streaming replication from a postgresql 9.2 master?
1
vote
2answers
399 views

To normalize or not to normalize for few distinct values

Assuming in a Postgres database, you have a table called party, which can have less than 5 well-defined party_types such as 'Person' or 'Organization'. Would you store the party_type in the party ...
2
votes
2answers
289 views

UPDATE table with another table data

I want to update the table car_check field fencing.(total 40 rows).I also use postgis function. I think it's a little difficult.Any suggestions? The code is the first carid example. UPDATE car_check ...
3
votes
1answer
2k views

Confused about load balancing and horizontal scaling in postgresql [closed]

Please correct me if I am wrong but I guess handling more requests and load by adding more machines or balancing the load between multiple servers is horizontal scalling. So, if I add more servers, ...
1
vote
1answer
76 views

PostgresSQL authentication error

I recently forgot my PostgreSQL 9.1 password on my windows 8 and it asks for password on pgAdmin III console. Can I retrieve the password or reset it?
5
votes
1answer
61 views

How to see amount of reusable space for a table

I had a huge table(about 300mil rows). After massive DELETE and VACCUUM table files have stopped to grow. That's exactly what I expected. But is there any way to see how much space became reusable?
0
votes
1answer
428 views

pgpool2 not working on my Ubuntu i686 athlon i386 GNU/Linux

I am trying to implement replication and load balancing on my Ubuntu i686 athlon i386 GNU/Linux.for creating database clustures I have installed postgresql contrib 9.1.11 having initially database ...
4
votes
1answer
120 views

Non-overlapping rectangles constrained to a boundary

I am trying to model placement of parts on a circuit board. Without any meaningful constraints, my basic schema looks like this: create table part ( part_id bigserial primary key, name text ...
2
votes
2answers
181 views

Order of returned record with IN statement postgres

I know that the order of returned records is not guaranteed with the IN statement for Pstgres. For example if I do this: SELECT "users"."id" FROM "users" WHERE "users"."id" IN (13589, 16674, 13588) ...
2
votes
1answer
283 views

Postgresql 9.2.6 ignoring archive_command

I'm attempting to set up a 3-server chain by leveraging WAL-E. I've done this in the past with replication/wal segments and I'm attempting the exact same method, just with using WAL-E in between. My ...
0
votes
0answers
44 views

postgres database program to save sql templates and search where used it

I have a postgres database with tables and functions build using sql templates. And I would like to use a database program to save these templates and search where I used it. What i want to do is to ...
1
vote
1answer
87 views

Battery backed write cache and Postgres

I will be getting a SAN with a battery backed write cache for a new Postgres data warehouse and am not sure what settings to apply to make proper use of it. If the cache is enabled in the SAN is that ...
1
vote
1answer
53 views

constraint view CASE values

Below is a sscce to motivate the case. I keep a table of persons: CREATE TABLE person (name VARCHAR); ALTER TABLE person add PRIMARY KEY (name); INSERT INTO person (name) VALUES ('Bob'), ('Mike'); ...
0
votes
0answers
1k views

PostgreSQL Install Error

When I install the server for the first time, when almost finished it reports: (from log file "%temp%\bitlock_installer1376.txt") Loading additional SQL modules... Executing cscript //NoLogo ...
2
votes
1answer
62 views

Selecting distinct records

I have a user relation to address (1 to 1). So the address has two types which is a type column. One is the VerifiedAddress and the other one is the SearchAddress. So the user who had verified his ...
0
votes
0answers
97 views

Unable to exit a Greenplum SQL script

Is there any way in psql (Greenplum flavour) of exiting a script? I have tried with \q and \quit, e.g. \echo "Hello"; \q -- or \quit but I get: my_database> \i my_script.sql ...
4
votes
3answers
578 views

PostgreSQL: Extending database storage when disk is full

I have a database that takes about 60GB in my disk storage. And it doesn't alow to insert into database anymore. I have read the documentation about tablespaces link But It's like create space for a ...
1
vote
2answers
1k views

Very slow execution of a simple query

I try to create a report for my data, but it is really slow on a big table. The table structure is: CREATE TABLE posts ( id serial NOT NULL, project_id integer, moderation character ...
0
votes
1answer
625 views

PostgreSQL, storing encrypted data or encrypt database with pgcrypto - what is more secure? [duplicate]

I have two machines: client and server (a database server). I want to connect from client to server and manipulate data from database (insert, update, delete, select) using dblink. I would like to ...
0
votes
1answer
454 views

PostgreSQL dblink password encrypted or not?

I have two PostgreSQL servers: server 1: 192.168.0.1 server 2: 192.168.0.2 I want to connect to the database on server 2 from server 1. I do it like this: SELECT ...
0
votes
1answer
481 views

Postgres database migration “zero” downtime

I have a setup with a JBoss EAP server, postgres 9.1 database. I will soon update so I have two JBoss servers in a cluster. This is mostly to avoid downtime when doing application deployments. With ...
1
vote
1answer
153 views

Optimizing Postgres query

I've got a one to one relation from users to addresses table. Where one user can have one search address and one verified address. I've got two indexes on addresses table : Index on state field ...