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)

4
votes
1answer
286 views

How to configure replication from PostgreSQL (RDS) to vanilla PostgreSQL?

I have an Amazon RDS Postgres DB and I created a "replicator" user and granted it the "replication" privilege. db=> create role replicator login password 'something'; CREATE ROLE db=> alter ...
0
votes
0answers
19 views

pgpool not selecting new master

I have a master-slave Postgres setup in basic streaming replication I'm trying to get behind pgpool-II. I have servers replicating find and connections to pgpool look to be getting load balanced ...
0
votes
1answer
154 views

Postgres 8.3 faster than 9.3?

Our software product currently ships on Windows 7 with Postgres 8.3 as its database. On a busy site, there may be 24 automated systems generating 100 rows (x100 columns) per minute, with 3-10 human ...
1
vote
2answers
69 views

Wrong index used in Postrgres

I have Postrgres 9.3 running on a Linux machine with 32GB RAM. I have a fairly large database (some tables with approx. 1 mil. records) and I have the following query: SELECT * FROM ( SELECT DISTINCT ...
3
votes
1answer
142 views

How to correctly return a query result only if it isn't NULL?

I'm writing a PL/pgSQL function that creates a cursor for a query that I need to check whether it returns something. What I'm doing is this: Run the query Check whether it returns something. If it ...
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 ...
0
votes
1answer
76 views

PostgreSQL: Cannot Connect to Server via PgAdmin, Password Auth Failed [closed]

I've been attempting to connect to my server's PostgreSQL database. The error message I am getting is "Error connecting to the server: FATAL: password authentication failed for user "postgres" I've ...
3
votes
0answers
75 views

Storing and querying rolling data in PostgreSQL

I have a large quantity of weather model data being put into a PostgreSQL database. The machine has 8 cores and 16 GB of RAM. I'm running PostgreSQL 9.3 with PostGIS 2.1. Each table will have a ...
0
votes
1answer
39 views

Subtracting dates give me error in Postgresql

I use age to get the interval between to date fields. Their type is timestamp. My code is as below: select age(timestamp MySecondDateField, timestamp MyFirstDateField) The data in my field ...
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 ...
0
votes
1answer
146 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
1answer
40 views

Rotate Postgres log at 5GB

How to make postgres 9.1 log to have maximum size 5GB and to be circle when comes to 5GB ? At the moment my log file is over 500GB. /var/lib/postgres/
4
votes
1answer
92 views

PostgreSQL does not use all available IOPS on a single operation

Using PostgreSQL 9.3 on Windows 7x64, CoreI7, SSD. Assume a PostgreSQL table with 8 Int32 columns and following C# code to fill the table. using (var connection = new NpgsqlConnection("User ...
-1
votes
1answer
66 views

Group by for UNION not working Postgresql

I can't find a way to make Group by works in Postgresql, How can i make group by works so each name will be only one , not repeated many times like what i got in this picture? Here is a code for what ...
4
votes
1answer
776 views

MongoDB performance vs. PostgreSQL with 5.5 million rows / documents

Can someone help me compare these queries and explain why the PostgreSQL query executes in just under 2000ms and the MongoDB aggregate query takes almost 9000ms and sometimes as high as 130K ms? ...
2
votes
1answer
46 views

How to create Select query function in the Postgresql?

I want create select function in postgres and get all data from table. I many example see and try but not perfact execute function. What perfact syntax for select query function create in postgres? ...
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 ...
0
votes
1answer
38 views

Postgres streaming replication to a different schema on another server?

We've got a system with about a dozen Postgres 9.3 databases representing a dozen different applications. In order to do reporting across systems, we have a script that dumps each database and loads ...
3
votes
1answer
52 views

postgres filtering select based on combination of joins

I am having trouble filtering a request that depends on a combination of child elements (one-to-many relationship) Please consider the following example. CREATE TABLE sample_container ( id serial ...
1
vote
1answer
94 views

Can't restore my distribution DB postgres 9.2 backup into my staging DB postgres 9.3

I keep getting this pg_restore: [archiver] did not find magic string in file header when i execute a pg_restore -Fc -U "username" -d "dbname" "filename".dmpon my staging DB. These DB are running on ...
0
votes
1answer
46 views

Issues with 'plus symbol' output in PSQL on update query

Simple but frustrating issue... I'm trying to do an update command to correct a misspelling with this field: grouper=> select * from master_list WHERE group ~ 'JustAdopted'; group | ...
0
votes
1answer
59 views

Postgres pgpool increase num_init_children

I am trying to tune postgre to allow many concurrent queries from clients as i am checking how many parallel connections with there queries it can handle. I read A good PgPool II configurataion , ...
0
votes
2answers
85 views

How to list all table columns in sorted order?

I know that \d table_name lists all columns for that table, but is there a way to list them sorted in alphabetical order?
0
votes
0answers
431 views

Postgres FATAL: could not open relation mapping file

I installed postgress.app on mac os when I try to connect to console : psql -h localhost I got this problem . psql -h localhost psql: FATAL: could not open relation mapping file ...
0
votes
0answers
82 views

Can't connect to local Postgres databases

After trying to install Postgres using Postgres.app and EnterpriseDB, I decided to re-install it using Homebrew (As I got some problems connecting to database). PYTHON=/usr/local/bin/python brew ...
0
votes
2answers
91 views

How do I use an Ispell dictionary with Postgres text search?

Postgres can use Ispell-compatible dictionaries in text search, but does not provide the required files.
0
votes
0answers
17 views

What configuration to use for PGbouncer over a WAN link

We have two datacenters, one in America and one in Europe. For various reasons, the Europe datacenter has the database server, while the workers and web apps are in America. PGbouncer is in the ...
0
votes
1answer
34 views

Postgresql Write Only Primary Server Bottleneck for Streaming Replication

I'm wondering about whether CPU/RAM plays an important role for the primary server for a Postgresql Streaming Replication setup where you have one primary server that receives all the write operations ...
0
votes
2answers
87 views

Can't run PostgreSQL locally

I am working to run PostgreSQL is running locally but I cannot connect. I installed postgress using brew, then I downloaded http://postgresapp.com When I try to create a database createdb: ...
2
votes
2answers
53 views

Postgres CREATE DATABASE already has data and tables

When I do a: DROP DATABASE mydb; and subsequently: CREATE DATABASE mydb WITH OWNER user; the new mydb database is created with all the tables and data that I had in the database I just dropped. What ...
0
votes
0answers
76 views

How to copy PostgresSQL database from remote server to LOCAL PC WITHOUT PG_DUMP(ALL)?

How to copy PostgresSQL database from remote server to local PC without pg_dump (or pg_dumpall ? Any format is good (plain text,sql,csv..) I have NO root access to remote PostgresSql database and I ...
1
vote
1answer
49 views

PostgreSQL - Create view with autoincremental column

I have a PostgreSQL table, and I need to create a view with a new column. This column needs to be an auto-incremental column starting at 1 and going to N. Is this possible to do without effecting the ...
0
votes
1answer
36 views

Postgresql Logging Questions

I'm seeking some help understanding my PostgreSQL 9.3 log file. (Note: maybe this should be multiple questions, they are all involve the same general topic - which is understanding my log file). In ...
0
votes
0answers
77 views

What size of data can PostgreSQL handle?

We wanted to use OpenERP along with PostgreSQL database. OpenERP has a functionality to upload documents and stored in to database in binary format. Our worry is if user decides to upload to huge ...
0
votes
1answer
245 views

Trying to restore a corrupt Postgresql database, with corrupt data folder. Able to start postgres but the tables are empty?

Postgres 9.3 on Ubuntu 12.04 We had some disk corruption on our server, and after doing a fsck the database would not start. To get us back into production, I copied the postgres data folder, blew ...
3
votes
1answer
143 views

Can I provide a default for a left outer join?

Suppose I have tables a (with column a1) and b (with columns b1 and b2) and I perform a left outer join SELECT * FROM a LEFT OUTER JOIN b ON a.a1 = b.b1 Then b1 and b2 will be NULL where a value of ...
0
votes
2answers
56 views

Postgres 9.2 - Create View Different Column Names

I want to create a view of a table with a column renamed. I do not want to effect the source table, just the view. I need to rename the column addr:housename to addr_housename. We have a 3rd party ...
0
votes
1answer
43 views

Appending imported data in Postgres

This is what I typically use to import csv data into my postgres database.. \COPY master_list FROM '/root/raw/file.txt' DELIMITER ',' CSV; Usually it's into a blank table, but in this case, I need ...
0
votes
1answer
81 views

Sync offline PostgreSQL server

I am new to PostgreSQL. I am looking for a solution wherein 2 mirror images of a database sit on a local and remote server. All the work is done on the local server and at periodically synced with the ...
0
votes
1answer
83 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 ...
3
votes
1answer
84 views

PostgreSQL: When should I use fillfactor < 100 for tables on SSD?

PostgreSQL documentation says that choosing smaller fillfactor lets table reserve space for future updates on the same page and "This gives UPDATE a chance to place the updated copy of a row on ...
1
vote
3answers
83 views

How to map similar camelcased, pascalcased, underscored, lowerscored words to the same id?

I'm working on PostgreSQL and I have a table like this called words_table: I need all equivalent words (like software_design and software-design) to have its map value = to its word's equivalent ...
2
votes
1answer
28 views

General purpose view for last value in a group

I am trying to create a view to show the last value in the group. Looking for a more general purpose query. CREATE TABLE a ( id INTEGER, seq INTEGER, val VARCHAR2(16), PRIMARY ...
1
vote
2answers
103 views

Select query to retrieve values even if are null

I need some help in a query. Need to retrieve data from 1 table, even if one of the field has null value. Here's an example: Select name, SUM(credit) as credit From expenses Where name like ...
3
votes
1answer
74 views

Determine the currently executing statement in a long running PL/pgSQL stored procedure

I have a long running PL/pgSQL stored procedure in PostgreSQL. How can I determine the currently executing statement in it?
0
votes
1answer
58 views

Importing data from MS Access database to an existing Postgres database

I'm developing a Django based website with a Postgres DB. The problem I'm having is importing the data from an Access DB into it. To be specific, I'm not trying to translate/import the schema from ...
5
votes
1answer
112 views

How to reclaim disk space on PostgreSQL?

I have local installation of 9.1 database with few tables which had cca. 300 mio records and the database grew to about 20 GB. Afterwards I issued delete from command to delete all records from it (I ...
0
votes
1answer
114 views

PostgreSQL user permissions

I have a PostgreSQL 9.3 database with the PostGIS extension backing a web application and GeoServer. I have created three users. A read only user for accessing the data in the reference data schema. ...
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: ...
0
votes
3answers
61 views

Postgresql: Make Column Default a Query

Is there a way I can set up a column in PostGreSQL 9.2 such that the default value is a query? I am having to track down query statements from different sources that don't respect the "default" ...