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
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 ...
4
votes
1answer
161 views

How do I monitor PostgreSQL WAL shipping if I'm not using streaming replication?

We have a fairly simple setup to replicate from our on-premise master PostgreSQL database to our presentation tier in AWS. We're using WAL shipping using the archive_command setting. Basically the ...
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 ...
-4
votes
1answer
75 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.
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 ...
-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'' '' '' ...
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
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 ...
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 ...
0
votes
1answer
147 views

How to add FK on a column of composite type in PostgreSQL?

I have a table which contains a composite type and I am trying to connect a column of it to another table's PK. CREATE TABLE aaa (col1 SERIAL PRIMARY KEY); CREATE TYPE bbb AS (col2 INTEGER); CREATE ...
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
91 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 ...
0
votes
2answers
16 views

Why we can backup postgresql database backup without password?

I want to backup my PostgreSQL database with pg_dump command that takes a lot of parameters. One of them is --no-password. Why this parameter is provided? I mean if anyone can backup database, so ...
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 ...
0
votes
1answer
1k 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 ...
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
35 views

restoring Postgres dump with tablespaces

I'm trying to restore a dump done with pg_dumpall > mybak.sql from a server that has 1 table in a different tablespace that the default one. On the target machine I have recreated the directory ...
1
vote
1answer
71 views

Retrieve title from rows with a maximum count in other columns

Given the following table archived: CREATE TABLE archived ( sid serial -- row id showurl text -- can be: btg, tmwwt, gcaf, emda title text reference text -- title id ...
1
vote
1answer
101 views

How can I get data from broken PostgreSQL server?

I have installed CentOS on a virtual machine and installed the latest version of PostgreSQL server 9.3. I have added PostGIS extension and some data. For some reason, the PostgreSQL service now fails ...
1
vote
2answers
27 views

Basic trigger in PL/pgSQL, to flip a boolean field if another field has text

I want a BOOLEAN field to be set to TRUE when a sibling TEXT field is set to a non-empty string. Vice versa, the BOOLEAN is set to FALSE when TEXT field is set to empty string. The following function ...
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
1answer
91 views

Choosing between single table and multiple table database scheme

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
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
votes
0answers
34 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
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 ...
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 ...
0
votes
1answer
36 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 ...
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
147 views

pgpool-II configuring multiple master databases

I could not find a solution for this anywhere, not even in the pgpool manuals. How do I configure multiple - master postgresql databases with pgpool-II? Additional requirement is that each master ...
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 ...
2
votes
2answers
694 views

save/export PGAdmin server list configuration settings

I have a ton of configured connections listed in the Server Groups, is there a way I can save this? not just save the passwords but the Server Group Settings
0
votes
1answer
60 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 ...
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 ...
2
votes
1answer
53 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
1answer
85 views

postgres pgpool2 replicating fails in ubuntu 12.04

I'm trying to configure a pgpool2 loadbalancer (in a Ubuntu 12.04 server) to balance between 2 postgres nodes (2 ubuntu 12.04 servers). The main problem is that, if I create a database using pgAdmin ...
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 ...
0
votes
1answer
330 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 ...
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 ...
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 | ...
2
votes
1answer
38 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 ...
3
votes
1answer
359 views

PostgreSQL High Availability/Scalability using HAProxy and PGBouncer

I have multiple PostgreSQL servers for a web application. Typically one master and multiple slaves in hot standby mode (asynchronous streaming replication). I use PGBouncer for connection pooling: ...
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
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: ...
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
1answer
58 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 ...
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 ...
6
votes
1answer
354 views

Is the optimisation fence behaviour of a CTE (WITH query) specified in the SQL:2008 standard? If so, where?

I see frequent references to WITH queries (common table expressions, or CTEs) acting as an optimisation fence, where the server isn't permitted to push filters down into the CTE queries, pull common ...