Tagged Questions

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
1answer
59 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
160 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
276 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
13 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
22 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 | ...
6
votes
2answers
140 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
138 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
69 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
1k 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
669 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 ...
3
votes
1answer
762 views

Postgresql subquery speed much slower than individual queries

In Postgres 9.1. I am attempting to do a single query to get the timezone of a given postal code. I have data loaded in my database, and I am using PostGIS to store the coordinates of each postal ...
0
votes
0answers
47 views

Recover the postgres data if hard drive fails in CentOs 6

How can I recover PostgreSQL the data if the hard drive fails? I am getting error like error: Invalid Page in block of relation /base/32769/12766 So I have corrupted my entire table - what should ...
0
votes
0answers
84 views

Unable to send WHERE parameters to subquery

I have the following problem (in PostgreSQL in combination with PostGIS): I have the following nice query who clusters multiple records. Although it uses PostGIS function, my problem is not PostGIS ...
2
votes
0answers
83 views

How to distribute a given database between multiple DBMS on a LAN? [closed]

Given a Database D, i need to distribute it over different DBMS in a LAN. D will be split into tables, and each DBMS will store a given table. Here is a sketch of what i need to do : I also need to ...
2
votes
2answers
925 views

Postgresql: Query across schemas

I have multiple schemas that I need to query across. The schemas are not know in advance for example: U111 U222 U333 I need to select across these three schemas from a specific table in each of ...
1
vote
2answers
247 views

How to do WHERE x IN (val1, val2,…) in plpgsql

I would like to update a selection of rows in a table; this works... UPDATE t1 SET col1 = 'newvalue' WHERE col0 in (2, 4, 5); But how can I do the same in a plpgsql function? The following gives a ...
1
vote
0answers
414 views

How to use the result of regexp_split_to_array() in stored procedure [closed]

I'm trying to write a function used by an trigger BEFORE INSERT on a table; the function should convert a raw GPS string to useable columns in a different table. The point where I get stuck is the ...
1
vote
1answer
108 views

Aliases with correlated subqueries

I am looking at an example of correlated subquery with aliases from a PostgreSQL book: bpsimple=# SELECT * FROM orderinfo o, bpsimple=# (SELECT * FROM customer c WHERE town = 'Bingham') c bpsimple=# ...
2
votes
0answers
37 views

Is there a way to make an OOo form for PostgreSQL updateable view?

I have a PostgreSQL database, with data from some 8 tables (not counting enums implemented as a table referenced by foreign key) summarized in a view. I made an INSTEAD OF trigger, so the view is in ...
2
votes
2answers
124 views

how to upgrade and migrate postgres dbs to new server machine?

my production databases run on a centos 5.5 virtual machine. This server has 2 postgres 8.4 clusters installed on 2 different partitions; now my idea is to upgrade both db version and server os ...
0
votes
0answers
48 views

How to config Enterprisedb work with PostgresXC ?

Util now, I created a cluster (PostgreSQL database + Postgres-XC) with 5 server: 1 GTM, 1 Coordinator, 3 Data Nodes. I intend to create a Enterprisedb (Postgres Plus Advanced Server, link: EDB's home ...
0
votes
0answers
64 views

How to create a cluster with 3 Master & 3 Slaves

I have 6 PostgreSQL database servers. I want to create a cluster with 3 masters, 3 slaves. Here my structure (what i need) master(A) <----> master(B) <----> master(C) (1) | ...
0
votes
0answers
44 views

Optimizing PostgreSQL 9.0.2

For work we have a 8GB Linode, hosting both the Rails 3 App (Passenger, Apache) and Postgres. The app is a reading heavy app, about 75% reading, and 25% writing. (A conservative estimate would be ...
3
votes
3answers
508 views

How to SUM previous sum, e.g N = (N-1) + (N-2) + … + 1?

I have a table name "TABLE_A (id integer, no integer)" . I want to sum "no" with group by "id" and current "sum of no" = previous "sum of no" Here is my code : 1/ Create table & insert data: ...
0
votes
0answers
88 views

What size of hardware/VM for database with over 1,000,000 posts, 20GB?

I am running a Rails application with a Postgresql database. I am exited to say that I am scaling rapidly, and I now have over 1,000,000 posts, not including the User table, in Postgresql(approx ...
0
votes
1answer
69 views

How to grant select on a view … based on IP adress?

I wonder if it's possible to allow a client A to "see" some data in a view while allowing any other clients, namely B and C, to see ALL data from the same view. The distinction between A, B and C ...
1
vote
1answer
83 views

Does the length of a table name affect performance?

PostgreSQL table names are limited to 63 chars. Is there a performance gain to be had if I limit my table names to something like 5 chars or can I use the full 63 chars without consequence? I ...
1
vote
1answer
165 views

INSERT using results of CTE INSERT to provide unique id values

I am writing a job to transform data from an old design into a new design. In this process, I need to take the id from an insert into a separate table and use that in an insert to the target table, as ...
1
vote
1answer
730 views

How to convert a unix time to PostgreSQL' Timstamp without timezome?

I have a PostgreSQL database running on a server which has its time zone set to India's Time Zone (i.e. UTC +5:30) I have some data in a table which is created like this: CREATE TABLE "CLOUDDATA" ( ...
1
vote
1answer
180 views

Vacuum in postgresql and Purge in mysql

Do the vacuum operation in postgresql and purging operation in mysql perform the same tasks?
4
votes
1answer
209 views

How to access NEW or OLD field given only the field's name?

I'm writing a validation trigger. The trigger must validate that the sum of an array equals another field. Since I have many instances of this validation, I want to write a single procedure and create ...
5
votes
1answer
896 views

Get WAL files from AWS RDS PostgreSQL instance

We have a Postgres RDS instance on Amazon Web Services. We have automatic backups enabled, and we take snapshots on a daily basis. We would like to generate a local 'up-to-date' backup of the RDS ...
1
vote
1answer
231 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 ...
3
votes
2answers
539 views

Replicating some tables from one postgres database to another

I've got the following situation: I have three machines running postgresql databases. One machine holds client account information (call this machine C), the other two machines hold client logging ...
1
vote
2answers
145 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 ...
1
vote
1answer
455 views

How to edit `postgresql.conf` with pgAdmin?

How does one edit the 'postgresql.conf' file with the pgAdmin app? With pgAdmin 1.18.1 on Postgres 9.3.x, when I choose File > Open postgresql.conf I get a file picker dialog. And, yes, I'm connected ...
1
vote
1answer
153 views

Self-join unique pairs

I have a table (players) containing a list of players, I would like to pair up these players in a unique way, so that every player's opponent is unique every round (ie. every time the SELECT query is ...
3
votes
2answers
2k views

PGAdmin3 connection with non standard SSH port and SSH tunnel

Hi I am trying to connect to Postgres from pgAdmin3 using SSH tunneling. However I am not using standard port 22. How can I do SSH tunneling using a non standard port?
0
votes
0answers
170 views

Performance measurement in PostgreSQL

When developing server-side stored procedures performance measurement is an important issue. EXPLAIN ANALYZE is very usefull, but when it comes to simple queries like SELECT name FROM clients WHERE ...
0
votes
2answers
80 views

compare dates in results

I'm looking to compare the dates in the result set Example: SELECT * FROM tbl WHERE foo=bar Output id, datetime, name 1, 2014-01-01 18:13:45, test 1 2, 2014-01-01 19:01:32, test 2 3, 2014-01-01 ...
3
votes
2answers
127 views

Compound Index on 43 Million PostgreSQL table

This question is related to a prior one of I asked: Order of columns in a compound index in PostgreSQL (and query order) Rather than overload that question, I figure I can sharpen and limit my ...
1
vote
0answers
60 views

Trouble adding foreign key on lots of data in Postgres

Working with PostgreSQL 9.0.8 on linux. Can't add foreign key. I have two tables: Table1 and Table2. Table1 has 10 columns and 200 000 rows filled with some data, where first column is 'id' (primary ...
1
vote
0answers
147 views

Reset postgresql [closed]

I have trouble: I tried to restore PG backup from CentOS on my Ubuntu computer. But had a trouble: after placing backuped Centos /var/lib/pgsql/9.1/data/ to my /var/lib/postgresql/9.1/main at Ubuntu, ...
3
votes
1answer
62 views

restarting postgresql quickly

I'm reading over the postgresql admin cookbook. Some of the database server parameters require a restart and with a busy database, you may need to restart quickly. There are a number of things to do ...
6
votes
2answers
484 views

Is this temp table behaviour documented?

Query Language (SQL) and PL/pgSQL functions treat temp tables differently: begin; create table foo(id) as values (1); select * from foo; /* id ---- 1 */ savepoint s; create function f() returns ...
0
votes
1answer
34 views

PgPool fails to auth in master_slave mode

We have two PostgreSQL servers running in master-slave streaming replication mode, behind pgpool. When we activate master_slave mode in pgpool.conf, we get following error: do_md5 failed in slot 1 ...
2
votes
0answers
493 views

Get postgres snapshot from Amazon RDS

I can not find a simple way to get a copy of the nightly backup I take of my Amazon RDS postgres instance so I can restore it on my local machine to play with. Right now I'm doing a pg_dump when I ...
2
votes
1answer
132 views

Has postgresql a fast add column optimization?

When adding not-null columns with a default value - does Postgresql optimizes this operation? In case the table has n rows, a not-optimized alter-table-add-column would yield n writes of the default ...
0
votes
0answers
31 views

Postgresql replica lagging behind

I restored a master database after a fallback by creating a new basebackup and making the promoted slave stand_by again. Per the logs everything is fine, however the following functions are not ...
0
votes
1answer
39 views

Postgresql: How to add a role that inherits from another?

Pretty cut and dry, I am just a total noob. Is there a way to make a given role inherit from another?