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
20 views

Organizing a couple tables in postgresql

I have a data structure that is represented like this: { 'question_id': 1, 'is_required': False, 'question_text': [ { 'id': 1, ...
3
votes
1answer
84 views

How to implement business logic permissions in PostgreSQL (or SQL in general)?

Let's assume I have a table of items: CREATE TABLE items ( item serial PRIMARY KEY, ... ); Now, I want to introduce the concept of "permissions" for each item (please note, I'm not talking ...
4
votes
2answers
77 views

select specified number of unique IDs where second column is unique

Look at the following example starting from the top row (id=9) and work your way down, selecting a limit of 4 rows that have sec's that we have not yet seen. We "select" id=9 because we don't yet have ...
1
vote
1answer
36 views

PostgreSQL 9.1: vacuumdb vs. autovacuum

Have a question on the difference between just allowing autovacuum to take care of vacuum'ing or having to run a full db vacuum in a certain interval (every night?, every week?). We used to run a ...
2
votes
0answers
69 views

Why does this query cause the Postgres worker process to consume so much memory?

I have a PostgreSQL 9.3.4 instance running in a CentOS 5.x VM (Linux 2.6.18-308.8.2.el5xen) with 6GB RAM. I've set shared_buffers to 1GB and work_mem to 1MB (a low value for testing). Here are my ...
3
votes
3answers
184 views

Join table on some conditions

I've got a table called activities. And a table messages. Activity has many messages, and one message has one activity. This are the message columns : id sender_id receiver_id body activity_id I'm ...
1
vote
1answer
50 views

Consolidating large table

I need some help with a complex query. Table def: Table "public.agent_log" Column | Type | Modifiers ...
6
votes
2answers
110 views

Improve performance of COUNT/GROUP-BY in large PostgresSQL table?

I am running PostgresSQL 9.2 and have a 12 column relation with about 6,700,000 rows. It contains nodes in a 3D space, each one referencing a user (who created it). To query which user has created how ...
2
votes
2answers
71 views

Problem translating MySQL to PostgreSQL

We're using Trac for issue tracking. I migrated it from MySQL to PostgreSQL, but some of the ticket queries no longer work. In the below, all I did this far is replace IFNULL() with COALESCE() and ...
1
vote
1answer
31 views

RETURN NEXT in Postgres Function

I'm currently writing a plpgsql function that will select from a table and produce a substantial amount of data. I was originally going to return an array, but I found a resource that explains the ...
1
vote
1answer
50 views

PostgreSQL: cache, disk, replication. What order?

I am thinking of using two servers in Master/Slave replication I want to add following condition: RAM size = dataset size * x, where x > 2. After that, I plan to force PGSQL to keep all data in RAM ...
1
vote
1answer
43 views

select and count based on a condition

I have two tables, proj: id name and proj_reports: id, position, created_at, key_id, proj_id and I want to select from key_reports for each proj_id: `- total number of `key_id` which can be done ...
0
votes
0answers
15 views

Is there a way to synchronise 2 postgresql server that may be deconnected and modified?

I would like to know if there is a way to use a cluster / replication mecanism that can manager deconnection and modification while deconnected. Say I have server A and B, they see each over, cluster ...
1
vote
1answer
22 views

Postgres startup fail error

I was running some code when my laptop shut down. Now everytime I connect to the server, I get an error message as follows: ambangra@access1:~$ LOG: database system shutdown was interrupted; last ...
3
votes
2answers
77 views

Privileges for database owner; application user

Quick version: What command should I issue to enable a database owner to allow it to access tables in this database and can this be done from that owner's account? Longer Version: I am creating ...
1
vote
1answer
67 views

How can I speed up a Postgres query containing lots of Joins with an ILIKE condition

I have an odd problem which I really don't understand. Simply put I have a join containing 4 table joins, which I believe all have appropriate indexes, but which takes a ridiculous amount of time to ...
0
votes
1answer
51 views

Postgres memory usage too low

I have a 400GB Postgres 9.3.4 DB running on Fedora20, and a few indices per table. I have upped shared buffers to 4GB (the system has 16GB available), set shm in the kernel and set the ...
1
vote
1answer
28 views

Preparing a former master to run as slave again

I have a streaming replication setup using postgresql 9.3. I have two servers, the master (server_1) and the slave (server_2). The other day I did a failover to my slave (server_2). Now server_2 has ...
0
votes
1answer
58 views

How to index WHERE (start_date >= '2013-12-15')

I have a table named items, and inside the table there is a some_date column, with date as the datatype. I was wondering how can I index the some_date column with PostgreSQL SELECT "items".* FROM ...
1
vote
0answers
19 views

How to see all queries via pgBadger

The excellent pgBadger has an interface to reviewing specific queries - the slowest, by host, by user etc. Is it possible to see all queries processed, perhaps in a given timeframe. If not available ...
-4
votes
1answer
80 views

What are the disadvantages of PostgreSQL compared to other DBMS? [closed]

I am a student studying PostgreSQL. I want to know the advantages and disadvantages of PostgreSQL in comparison to other DBMS.
0
votes
0answers
41 views

Reduce read write delays in specific database setup

I have a database with a large table with 15 columns (type double). It contains a large amount of data (~ 100 rows of data for every second. Holds 3 days of data). I also have a real-time program that ...
0
votes
0answers
47 views

Lost all my Postgres database tables? [closed]

I'm a novice using PostgreSQL on a local database that I created using Postgres.app on my Mac. My dbase included multiple related tables with ~50MB of data, and after restarting my Mac (system update) ...
1
vote
1answer
33 views

PostgreSQL : send all rows of a table to a function

I have a table with one column (double precision numbers). I also have made a function (with double precision numbers as input and output), and I want to send every value of my table to the function, ...
1
vote
0answers
51 views

Howto debug sudden crazy high CPU usage

I'm running postgresql 9.3 on a production server. An hour ago, out of the "blue", I ran into an issue I have never encountered before: my server started to use CPU as crazy. The server is a standard ...
1
vote
1answer
53 views

Organizing Postgres Databases

Is there a way in pgAdmin to organize Postgres databases into "folders" or groups? Each Django project I'm creating has it's own Postgres database, and I would like to find a way to organize them. I ...
2
votes
6answers
92 views

Efficiently storing irregular/repeating intervals (think calendar/events)

I am developing a service that relies on users being able to recieve messages that they themselves choose. These messages need to be stored somewhere before they are send for processing. Right now ...
2
votes
1answer
64 views

How to reuse an update trigger for multiple tables in postgresql?

In the database for a new project, I've started the convention of having each table have a last modified timestamp column. To implement this, I wrote a trigger for each table: CREATE TRIGGER ...
0
votes
1answer
39 views

Postgresql Database on external HD

I'm completely inexperienced with PostgreSQL, but have to do some work on it, and I have been googling for a solution for almost two days. I hope someone here can help me out. I have a PostgreSQL ...
0
votes
0answers
39 views

Granting permissions to PostgreSQL user

I am trying to grant permissions to one of my Postgres users. I am running CentOS with PostgreSQL, Ruby On Rails 3, ruby 1.9.3 postgres=# GRANT ALL PRIVILEGES ON loadmax TO loadmax; ERROR: relation ...
-1
votes
1answer
41 views

Select Into Regexp_replace doesn't write changes into newly generated table

I have been using this query in postgres 9.1 in order to remove '' from existing data. Below is a sample of the data before running regexp_replace. 6'' 6'' 6'' 20'' 12'' 18'' 20'' 8'' 10'' '' '' ...
-1
votes
0answers
35 views

Postgresql Database Disk Size Index Optimization

I would like to ask how can i optimize index disk size, I am using two index for my table and taking up a lot of disk size. I am using Btree as index type. My current Postgresql version is : ...
0
votes
0answers
29 views

“Database user”, “application user” and “person” and mapping between them in PostgreSQL

In my PostgreSQL database I have a table for people and a table for users, connected with a 1:0..1 relationship. The "user" table can serve as a list of users for the client application, but not for ...
2
votes
0answers
28 views

Why does returning a custom data type appear to cause a memory leak in my PL/Perl stored procedure?

I have recently encountered what appears to be a memory leak with a particular long-running postgres process, which runs a stored procedure. I am able to reproduce the same behavior with a very ...
0
votes
1answer
37 views

Do I need to set tcpip_socket = true to allow remote connections to a postgresql database?

I am trying to enable remote connections to my postgresql 9.1 db running on ubuntu 12.04. This tutorial says to "set tcpip_socket to true" -- but it is for a centos server. I am on ubuntu. I ...
0
votes
1answer
47 views

Add 0 into array_agg if there is no matching data

I wrote this view, and it is selecting almost everything I want, but I do not get results for all: project_keywords(which has_many project_report(keyword_id, project_id)) So a project_keyword has a ...
5
votes
1answer
55 views

What happens in PostgreSQL checkpoint?

Here's part of my checkpoint log: 2014-03-26 11:51:29.341 CDT,,,18682,,532854fc.48fa,4985,,2014-03-18 09:15:24 CDT,,0,LOG,00000,"checkpoint complete: wrote 15047 buffers (1.4%); 0 transaction log ...
0
votes
0answers
16 views

database filesystem backup error

I get this error earch time: postgres=# select pg_stop_backup(); NOTICE: pg_stop_backup cleanup done, waiting for required WAL segments to be archived WARNING: pg_stop_backup still waiting for all ...
1
vote
2answers
59 views

psql: id SERIAL PRIMARY KEY not starting with 1?

This is something I don't understand, can somebody explain? I create a table CREATE TABLE cali (id SERIAL PRIMARY KEY, ALK_from char, ALK_to char, AND_from char, AND_to char); Then ...
1
vote
1answer
60 views

Postgresql view how to do the query

I have 4 tables: Project: id name Keyword: id, name ProjectKeyword: project_id, keyword_id (project has many keywords, keywords has many projects) ProjectReport: project_id, keyword_id, ...
0
votes
1answer
49 views

Postgresql create database from backup programmaticly [closed]

I got a big problems here. Let's say I got the table A (with columns like id, name etc) in the main database. So when I making INSERT into A I need to create new database lets say B_id. I got .backup ...
1
vote
1answer
76 views

Restoring the superuser account on the postgres server

I have a complete curious situation with a postgres dbms. I have installed the database and due to a misconfiguration of my installation scripts I made the superuser role to be a regular user. Now I ...
2
votes
1answer
39 views

Are TCP loadbalancers an alternative to Postgres 9.X read slaves load balancers like pgPool II or pgBouncer?

To scale read traffic on PostgreSQL streaming replication slaves, I want to be able to load-balance the requests. Postgres documentation suggests using tools like pgPool and pgBouncer, but I am ...
0
votes
0answers
10 views

In Amazon Redshift, running GRANT as a superuser does not grant privileges on table to a regular user [duplicate]

I'm trying to run a query in Amazon Redshift, but I keep getting the error permission denied for relation mytable As far as I understand, this is a privileges issue. According to this question: ...
1
vote
1answer
12 views

Splitting metadata inside Postgresql

I have metadata in a postgresql table that I need to split. So far I have been attempting to use Select col_name from "tbl_name" split_part('QGC|WGPLNW|IR|BBC|FCPC|WGPLNW|ARME|QPC', ", 1 | 2 | 3 | ...
5
votes
2answers
72 views

Altering db schema in production environment

I have some questions on db schema changes in production environment without taking the database server and the webapp offline. Q1- I want to be able to make changes to database schema (adding, ...
1
vote
0answers
26 views

how do I debug postgresql 9.1 server terminating

my /var/log/postgresql/postgresql-9.1-main.log shows 2014-03-25 10:39:30 CET HINT: In a moment you should be able to reconnect to the database and repeat your command. 2014-03-25 10:39:30 CET LOG: ...
0
votes
1answer
32 views

How to ensure a trigger is fired after variable number of inserts?

I have a 1:N relationship between two tables - let's call them "foo" (the parent table) and "bar". I have some triggers on both "foo" and "bar" tables. I insert data in a single transaction, first to ...
3
votes
1answer
67 views

Postgres is stuck in recovery mode

I have a stand-alone instance of PostgreSQL which is in recovery mode. It has been saying 2014-03-24 18:45:57 MDT FATAL: the database system is starting up For many hours, and ps shows: postgres ...
0
votes
1answer
41 views

What is best strategy for backup on streaming replication(hot standby) environment

I have questions. What is best strategy for backup on streaming replication(hot standby) environment. I’d like to do backup on slave server and restore(PITR). First, I set up streaming ...