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
0answers
3 views

Error regarding SHMMNI parameter in your kernel durign the install of Postgres 9.3

During the install of Postgres 9.3 I get... FATAL: could not create shared memory segment: No space left on device DETAIL: Failed system call was shmget(key=1, size=36, 03600). HINT: This error does ...
0
votes
1answer
8 views

could not connect to pgpool server

I am configure my pgpool server to allow access from: host all all 0.0.0.0 trust The service pgpool is running: user@pgpool:~$ sudo service pgpool2 status * pgpool-II is running. ...
3
votes
2answers
539 views

Why does Postgres generate an already used PK value?

I'm using Django, and every once in a while I get this error: IntegrityError: duplicate key value violates unique constraint "myapp_mymodel_pkey" DETAIL: Key (id)=(1) already exists. My database ...
0
votes
0answers
4 views

PgBouncer and Application Connection Pooling

I understand that PgBouncer is a dedicated component to handle connection pooling and my application would connect to PG server through PgBoucer. The connection pooling on PgBouncer side is strongly ...
1
vote
2answers
20 views

PostgreSQL - summing arrays by index

I have an array of doubles column (double precision[]) in PostgreSQL that keeps half hour values for a day. So each array holds 48 values. I need an efficient query that is summing all this array ...
1
vote
1answer
15 views

List distinct column values where those rows share other column values

I'm trying to write a query to detect possibly-invalid data in a PostgreSQL table. We have a table of city names like this: # `city_names` id | name | language | dialect | city_id ...
0
votes
1answer
11 views

How to restore postgreSQL schema dump to schema with different name

I know I can alter schema name after restoring but the problem is the name might already exist and I don't want to touch that existing schema. The dump type is "custom". So effectively I want ...
0
votes
1answer
129 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 ...
0
votes
0answers
6 views

Does PGStrom calculate index comparisons?

I prefer index calculation and comparison be executed on the GPU. I have stumbled upon PGStrom. It seems one could infer that it will calculate index comparisons, but the claim is not made ...
1
vote
1answer
19 views

PostgreSQL complaining about shared memory, but shared memory seems to be OK

I've been performing kind of intensive schema dropping and creating over a PostgreSQL server, but now complains..: WARNING: out of shared memory ERROR: out of shared memory HINT: You might need to ...
1
vote
1answer
66 views

BitmapIndexScan vs IndexScan tuning

I have the following test case: CREATE SCHEMA IF NOT EXISTS long; SET search_path TO long,pg_temp,pg_catalog; CREATE TABLE long WITH (autovacuum_enabled=off) AS SELECT id, (random()*1000)::int AS ...
1
vote
1answer
10 views

Connection pooling with 3 databases but 1 database as master for repmgr replication?

I'm using pgbouncer for connection pooling against 3 database on different servers, now I'm planning to use repmgr for replication, my question is, can I keep doing connection pooling on these 3 ...
0
votes
1answer
139 views

psql: could not connect to server: No such file or directory

The error in its entirety reads: psql: could not connect to server: No such file or directory. Is the server running locally and accepting connections on Unix domain socket ...
1
vote
2answers
64 views

Limiting read access in postgres to few rows

We have a requirement where we want to limit the maximum no. of rows that a select query can return when its done using a particular login. So basically we would like to append a limit clause to any ...
3
votes
1answer
275 views

Could not open file “pg_clog/0000”: No such file or directory

My local PostgreSql 9.2 database won't start anymore this morning. I am on Windows 7. Yesterday, I performed a Windows Update before going to bed. I have checked the log and found: 2014-05-28 ...
0
votes
0answers
26 views

Using Configuration Management for DBA tasks?

We have a couple of Postgres 9.3 database servers in our system, all of them with their respective config files, databases, roles, role groups, permissions on schema/tables, extensions etc. We use ...
1
vote
1answer
57 views

PostgreSQL performance worse when adding CPUs

I ran into a strange behaviour when benchmarking PostgreSQL. The setup is like this: Virtual box VM, with 3 GB of RAM. The host has 16GB of RAM and 4 CPUs. Ubuntu 14.04, standard PostgreSQL 9.3 ...
0
votes
2answers
26 views

for archive_command use an script rather than a command

I want to create a configuration to get a PostgreSQL HA. In my Master I want to configure a postgresql.conf file with this: archive_command = 'local_backup_script.sh "%p" "%f"' instead of: ...
1
vote
2answers
24 views

Why wouldn't VACUUM ANALYZE clear all dead tuples?

We run a "manual" VACUUM ANALYZE VERBOSE on some of our larger tables after we do major DELETE/INSERT changes to them. This seems to work without issue although sometimes a table's VACUUM job will run ...
0
votes
1answer
197 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 ...
0
votes
0answers
17 views

Postgresql: Domain of two values

I have to create a custom domain that is an interval between two years, but i actually need both years. For example: '2013/2014' '2015/2016' Is there a way to pack both years inside a single ...
0
votes
1answer
61 views

Why does 'plpgsql' not appear in the pg_extension table?

I have two Postgres 9.1 installations. One works properly, the other does not. On the working installation: dc=# select * from pg_extension; extname | extowner | extnamespace | extrelocatable | ...
0
votes
1answer
13 views

How to connect to an Amazon PostgreSQL database using SSL

I'm trying to log into an Amazon PostgreSQL DBS using SSL. I have all credentials but my psql version doesn't seem to support SSL. I've tried searching Postgres docs but all I see is how it has ...
40
votes
2answers
15k views

How can I time SQL-queries using psql?

I would like to benchmark some SQL-queries agains my PostgreSQL database. Is there any way I can time SQL-queries using psql?
1
vote
1answer
27 views

How much should I care about prepared statements for simple queries?

I wonder how much I should care about prepared statements (and therefore try to optimize my code) in case of pretty simple statements. The question originates from the fact that I recently noticed ...
1
vote
1answer
19 views

Format timezone to +-#### instead of +-##

I am curious if there is a way to configure Postgres (9.1+) to format the TZ part of a "timestamp with time zone" as +-####, just like PHP's date.format method does using "O". PHP Docs: O | ...
-1
votes
0answers
24 views

Get records that have associations in other tables [on hold]

I have multiple tables such as dataset_tb, dataset_tag_tb, user_dataset_access_tb. I need to get datasets that have a specific dataset_type value in dataset_tb, in which there exists a record in ...
1
vote
1answer
14 views

Does pg-pool-II support two phase commit?

When pg-pool II is used with sharding, what happens if I want to do a transaction which involves two nodes ? Does pgpool support two phase commit ?
0
votes
1answer
14 views

Revoke delete from table for database owner

I am trying to revoke the DELETE privilege on a table for a database owner. I execute the command: REVOKE DELETE on my_table FROM database_owner; But when querying this via SELECT ...
3
votes
2answers
7k views

psql denies access to file when using COPY FROM

When I do this: COPY "mytable" FROM '/my/file.csv' WITH DELIMITER AS ',' CSV; On this: -rw-r--r-- 1 peter peter 54819176 2011-07-21 13:17 file.csv psql tells me this: ERROR: could not open ...
0
votes
0answers
12 views

pgAgent does not run

I referred this to set up a scheduled job and did everything as it mentioned(ran the pgagent.sql as well) I need a very simple task to run periodically. In the 'Definition' tab in the steps, few ...
1
vote
1answer
27 views

Replicating a postgres database for testing

I want to replicate a live server postgres database to our dev server for testing purposes but the problem is in inbuilt postgres replication feature the slave is always read only. Is there any better ...
1
vote
1answer
10 views

Proxy authentication for PostgeSQL

The following seems likely to be a fairly common situation, but I haven't seen any solutions. I have a PostgreSQL database containing views that are specific to the $user, in order to restrict data ...
0
votes
1answer
17 views

How the xlogs files travels form Master to Slave

I have some doubts about how the workflow proccess in PostgreSQL replication using archiving logs. If I have this configuration in Master postgresql.conf: archive_command = 'test ! -f ...
0
votes
0answers
13 views

Recursive query to break time intervals into standard-sized parts

I'd like to write a reusable function which takes 3 arguments a timestamp start_time a timestamp end_time an array of intervals interval_array such that each interval (except the first) divides the ...
0
votes
0answers
31 views

Hardware Specifications [on hold]

I'm having trouble determining what hardware to get to host a large (2-3TB) Postgres DB. The database will only have at most 5 or 6 users, and be able to output large queries that will span the whole ...
1
vote
1answer
2k views

Installing and configuring postgresql 9.3 on ubuntu 13.10 using puppet

I'm having some trouble getting postgresql[1] to install with puppet[2] on a blank ubuntu box. I need to install postgresql 9.3 on Ubuntu 13.10. Since postgresql 9.3 is apparently not in the official ...
0
votes
1answer
166 views

Postgres DB - Commit xlog files after server crash

After a server crash, it would seem that all transactions going back a number of months are not showing up in the live Postgres DB. Investigation has found all xlog files that does contain the data ...
0
votes
3answers
5k views

Export Postgres table on remote server to CSV file on local machine?

I have access to DB on remote server where I can do only select command, So, I can execute: COPY products TO '/tmp/products.csv' DELIMITER ','; But on that server I don't have permission to ...
0
votes
1answer
22 views

PostgreSQL : drop column from view

I have a VIEW where I'm attempting to create an evolution script for, so I can add a column to it. That part works fine; column added just fine. However, the reverse does not work; remove that last ...
0
votes
1answer
45 views

Use serial ID for multiple columns and return it from INSERT

I am using Npgsql and I want to be able to do an insert, and if statement that will do an update and then return a value so when ExecuteScalar is called it reads that value. This was done fine in ...
2
votes
3answers
46 views

Change table scan in query plan to seek

I have an ORM generated query (Django) running against a local Postgres 9.1.11 instance that looks up ~5 records from two specific match fields. Due to the nature of the EDI load program, I need to ...
1
vote
1answer
15 views

Find the number of where statements that matched in postgresQL

Right now I have a query that looks something like this SELECT id FROM products WHERE products.details->'make' = ? OR products.details->'model' = ? OR products.details->'year' = ...
0
votes
0answers
4 views

“kind does not match” after failover

We are using pgpool-II 3.3.3 with 4 nodes of Postgres 9.3. At some point today 2 of the 4 nodes had errors and pgpool did a successful failover. When the 3rd node had errors pgpool also reported a ...
0
votes
0answers
17 views

PostgreSQL is not installing correctly

I am installing postgreSQL in windows 7 64 bit system. The problem I am facing is that the setup is not finishing properly, the window showing installation is not terminating. The icons are visible ...
0
votes
0answers
17 views

Not able to create or modify tables using Oracle SQL Developer with psql driver

So I would like to use the Oracle SQL Developer for managing my postgresql database. Connecting to that database works fine with SQL developer 4.0.3. I am using this psql driver: JDBC41 Postgresql ...
2
votes
1answer
41 views

Combine a generated series with each row from a select

The below select is returning an array of all the projects owned by all my users. WITH user_projects AS (SELECT u.id as user_id ,COALESCE(array_agg(DISTINCT pa.project_id), '{0}'::integer[]) as ...
0
votes
1answer
25 views

Compare the same column on different dates

I have two tables project_key: project_id, key_id project_report: project_id, key_id, level, created_at I would like to compare the column level when created_at is now() and created_at is ...
0
votes
1answer
14 views

How to change the mount point for a column in postgresql table?

This is my scenario I have a table t with following columns id int link varchar(500) name varchar(20) sal text In the link column we will store the location for the pdf files(mount point) ...
1
vote
1answer
186 views

Trouble with installing additional modules on Postgresql 9.1 on ALT-Linux

I'm trying to create extension under postgresql 9.1 (Linux): CREATE EXTENSION pg_trgm; but got error: ERROR: could not open extension control file "/usr/share/pgsql/extension/pg_trgm.control": No ...