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)

1
vote
1answer
20 views

Meaning of query in pgbouncer.log

I could not find any documentation about pgbouncer log, I'm assuming it has the same meaning as the SHOW STATS ? req/s - number of requests since last stat period in b/s - Bytes per sec received ? ...
0
votes
1answer
29 views

Does PostgreSQL write first to WAL and then to data files?

This is regarding the synchronous_commit in Postgres. The synchronous_commit documentation says: Specifies whether transaction commit will wait for WAL records to be written to disk before the ...
0
votes
1answer
17 views

Creating unique constraint to be validated from input

I have this table name tbl with this table definition: create table tbl ( id serial primary key not null, counter integer not null default 1, ref_id integer not null, date_booked date ...
0
votes
0answers
13 views

difference between pg_dump command's --column-insert and --attribute-insert

Background: I'm trying to create a dump file that only contains the data in my database. I would like it to create the data with the INSERT command. In reading the manual, ...
1
vote
2answers
47 views

Building a Trending query

Is there a way to build a query that returns a record set consisting of a date and the number of records == that date? For example, I have a DB that is tracking inspections that take place on every ...
2
votes
1answer
80 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 ...
1
vote
0answers
24 views

Pgpooladmin pcp commands does not work [closed]

After first installation of pgpoolAdmin I got an error e1002 with message "pcp_node_count command error occurred" Does anybody know why is this happen and how to fix this? Little update: just ...
1
vote
0answers
35 views

Does amazon rds PostgreSQL require vacuum?

It isn't clear if auto vacuum is running in rds. Thanks!
1
vote
1answer
51 views

How does MS SQL Server authenticate MSDTC connections when using SSPI?

I'm trying to get SSPI authentication to play well with two-phase transactions via MSDTC, using PostgreSQL with psqlODBC. I can't figure out how it can work securely - so I'm hoping someone can ...
1
vote
1answer
30 views

How to design a versioned database with PostgreSQL?

I am planning to create a back-end aka an API. The database will be PostgreSQL. I would like to discuss the following scenario: How can I have multiple versions of the same database? I would like to ...
3
votes
2answers
45 views

How can I fake inet_client_addr() for unit tests in PostgreSQL?

I have a simple stored procedure whose return value depends on the value of inet_client_addr(). How can I override inet_client_addr() for the purpose of unit tests when testing my stored procedure? ...
1
vote
0answers
22 views

Postgresql service frozen and can restart properly

I have windows server 2008 R2 and postgresql 9.3 Server is still for testing so I'm alone and only have a .net application doing consult with npgsql to the db, mostly doing some store procedures and ...
0
votes
1answer
33 views

How do I go about easy table input without creating an application? [on hold]

I am converting all my applications from MS Access to PostgreSQL and I have a few personal-use databases. These MS databases have text fields as the primary keys, so I can type the name of something ...
0
votes
0answers
23 views

Postgresql startup: runuser: cannot set groups: operation not permitted

I'm having trouble starting a postgres database. I'm running 9.2 on Centos 5.4 (for compatibility reasons). Here is what I have done so far: Ran postgres initdb for the directory /opt/database ...
0
votes
0answers
17 views

Starting up postgres on Ubuntu for the first time

I've installed postgres on ubuntu, created a node and now I'm trying to start up that node as per the instructions: postgres --coordinator -D /usr/local/pgsql/data -p 1281 But I get the following ...
0
votes
0answers
34 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 ...
4
votes
0answers
42 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 ...
1
vote
1answer
20 views

Pgpool - semanage config files - which filetype should I choose?

Reffering to problem with pgpool installation : Problem with pgpoolAdmin installation I had a problem with non running pgpooladmin(installation files permission was wrong). I must change file label of ...
1
vote
1answer
67 views

Very slow simple PostgreSQL query on RDS

I seem to be getting very slow queries on a medium sized RDS box (db.m3.medium, 3.7gb ram). This is across a table of 4,152,928 rows.. select sum(some_field) c from pages where pages.some_id=123 and ...
2
votes
2answers
144 views

How many database servers do I need?

This may be a no-brainer to some of you, but I am a newcomer to DBA and this question popped into my head and I couldn't answer it on my own and so I am turning to the experts here. I have a app that ...
1
vote
1answer
34 views

Detailed optimal column order with large text and bytea

I read this excellent answer on optimal column ordering and noticed the summation: Generally speaking, if you put the 8-byte-types first then the 4-byte-types and the 2-byte-types last you can't ...
0
votes
1answer
29 views

PostgreSQL: how do I query over a column with a custom range?

I'm trying to write a query over a string column called grades_served that can have values such as k-5 1-5 5-9 The query could be for a specific grade or it could be a range with a start and end ...
0
votes
0answers
21 views

How does ActiveRecord handle hard drive block size in PostgreSQL? [on hold]

I know that hard drives are split into blocks of bytes aka chunks, that if used less then the total amount of the block the remaining space is an empty waste of unused space. What I'm curious about ...
1
vote
1answer
29 views

How to configure read transactions to never cause other write serialization transactions to fail

I am protecting critical transactions with application mutexes and SERIALIZABLE. I presume that this makes these operations as acid as possible, but now I'm concerned that reads might cause failures ...
3
votes
1answer
54 views

Query with dynamic ranges in PostgreSQL based on status

We have a table with invoices classified per day, with normal invoices and void invoices (invoices that are voided, in our country we use the Spanish term "anuladas"). For example suppose that we have ...
0
votes
1answer
33 views

PostgreSQL : inconsistent result when using WHERE clause on VIEW

This is my VIEW declaration CREATE OR REPLACE VIEW inv_groups_vw AS SELECT gg.id, gg.sku, gg.name, gg.description, CASE WHEN count(i.*) = 0 THEN '[]'::json ...
1
vote
1answer
35 views

Problem with pgpoolAdmin installation

I got a problem with permissions during installation of pgpoolAdmin(newest version). The problem is when I get to window with main config that should be all okay to move to next installtion step. The ...
2
votes
1answer
55 views

MS SQL - export tables to format compatible with PostgreSQL

We're using MS SQL Server 2005 at work and I want to export our data to PostgreSQL for testing. MS SQL Server has an export function but the only one that looks like it would work would be to export ...
1
vote
0answers
27 views

Is a commit or abort more costly for a prepared select?

I have found that selects will actually generate WAL records upon commit. Are there other costs to committing a select? Are there costs due to an abort? If so, which has more? What would be the ...
3
votes
0answers
47 views

Will a serializable read only deferrable select block?

I'm unsure from the documentation and wiki if a serializable read only deferrable select, a SELECT preceded by SET TRANSACTION ISOLATION LEVEL SERIALIZABLE READ ONLY DEFERRABLE, will block a ...
0
votes
0answers
18 views

Postgres sync database

I have two postgres databases. Master = master_database Slave = database All initial changes are made to master. Upon admin approval, the version is updated, and I want all changes to be copied ...
1
vote
1answer
40 views

Adding a group of rows in postgresql based on column id

I have a table with a sets of rows such as displayed below: Qty Equip_ID 21487.92;"Load Center-6" 99272.59;"Load Center-10" 68088.61;"Load Center-11" 21821.5;;"Load Center-6" 102165.2;"Load ...
0
votes
1answer
31 views

PostgreSQL : select columns inside json_agg

If I have a query like SELECT a.id, a.name, json_agg(b.*) as "item" FROM a JOIN b ON b.item_id = a.id GROUP BY a.id, a.name; How can I select the columns in b so I don't have b.item_id in the ...
3
votes
1answer
37 views

PostgreSQL seems to ignore RAISE EXCEPTION in a CTE

Using PostgreSQL 9.3 I have been trying to define an assert helper function to check for empty query results and similar things as follows: CREATE FUNCTION public.assert ( in_assertion boolean, ...
0
votes
1answer
22 views

How do I select the value with SERIAL data type in postgres

I wanna ask how to select coloum that has SERIAL data type. I already try to do that but the output is like this 'Resource id #3', and not just '3' I use SERIAL so I dont have to input ID like when ...
0
votes
1answer
26 views

Selecting 3MB (or more/less) data from database server

I installed PostgreSQL Server on my Windows 7 machine. I created database and a simple table, and populated the table. Now I want to connect through the network with my Postgres server and select a ...
0
votes
0answers
30 views

Will Amazon RDS for Postgres be as convenient as using Heroku Postgres?

I'm currently running a Heroku Rails app and our Postgres DB has grown to a data size of 50GB, and our DB cache hit rate is at 93%. We're on the legacy Ronin ($200/month) plan (1.7GB) and probably ...
0
votes
1answer
32 views

Will removing a foreign key constraint affect performance?

I have a units table and a reports table. A unit has many reports. So reports table has a foreign key unit_id which corresponds to the id of the unit. Hence, there is a foreign key constraint in ...
0
votes
1answer
35 views

Postgres : Executing SELECT within a transaction does not return most recent rows

Given a table layout that looks like this inv_items ========= * id bigserial sku character varying(22) name character varying(32) ... inv_items_stock =============== item_id bigint (inv_items) qty ...
3
votes
0answers
32 views

Postgres multidisk master replicating to single disk slave

I have an odd situation. I have a postgreSQL database master with multiple large tablespaces divided across 3 physical disks. I am setting up a slave that will be used to offload long, ...
0
votes
1answer
42 views

PostgreSQL: Creating view of multiple tables in function

I want to create view of multiple table in function using PostgreSQL 9.3 version. Example: I have following tables in the table name "AllTables": table1, table2,table3 And I have following ...
1
vote
1answer
33 views

How to insert into view through GUI in pgAdmin?

I have some updateable views for inserting the data. The reason is not just "security layer", inserting into some 10 tables directly would be a pain without the view with INSTEAD OF trigger. In ...
1
vote
0answers
30 views

How many Maximum Number of database can be created in single instance of PostgreSQL? [closed]

How many Maximum Number of database can be created in single instance of PostgreSQL 9.x?
2
votes
1answer
32 views

Create a new table (multiple columns) by selecting random values from an existing table

I am stuck on the following task (PostgreSQL 9.3). Let's say we have the following table1 (which has 10k rows): table1: id 754 800 330 4 59 My goal is to create another table2 with two columns ...
1
vote
1answer
31 views

What will GRANT <privilege> ON ALL TABLES IN SCHEMA <scheme> TO <role> do?

It seems obvious that PostgreSQL will GRANT some privileges ON ALL TABLES IN some schema TO some role when I'll command. But, will it make it permanent for the schema that on all tables within it, ...
2
votes
1answer
26 views

PostgreSQL Automatic Failover (Trigger File Creation)?

Is there a way to detect if the master PostgreSQL (9.3) server becomes unresponsive, and if so, create the trigger file (that is specified in recovery.conf) that initiates the failover process? Or ...
0
votes
1answer
30 views

Can PostgreSQL results include the query?

I run the following command to run 2 queries in a sql file. Could be 1, could be 50 queries. Depends on the deployment. psql -U <username> -h <servername> -d <databasename> -f ...
1
vote
1answer
22 views

Postgres Update, limit 1

I have a postgres database which contains details on clusters of servers, such as server status (active, standby, etc). Active servers at any time may need to fail over to a standby, and I don't care ...
0
votes
1answer
44 views

Postgres table growing on massive updates

I have an issue with my postgres database, I'm running massive update queries (1000 per second) to a single table (with 3000 entries) and I can see that the size of that table is growing and growing ...
0
votes
1answer
27 views

How to relate to information_schema

Is it possible to create a foreign key that relates to information_schema? For an application implementation, I would like to store extra column information in another table, but I want to ensure ...