PostgreSQL 9.3 : 2013 release of PostgreSQL

learn more… | top users | synonyms (1)

0
votes
0answers
7 views

How to install Slony for 64-bit PostgreSQL 9.3 on Windows?

I have installed 64-bit version of PostgreSQL 9.3.5 from EnterpriseDB installer. Now I want to install Slony-I through Application Stack Builder, but it offers only 32-bit version of Slony, ...
0
votes
1answer
14 views

Insert any type of text in a column

How can I insert any type of text into a column of type text? At the moment I am using this: $$description$$ But a lot of my descriptions contain $ and other signs, so the insert will fail. Do I ...
1
vote
1answer
13 views

“Wrong affix file formate for flag” while loading dictionaries from LibreOffice in PostgreSQL 9.3

Trying to get good TEXT SEARCH results out of PostgreSQL in English, German and Dutch made that I downloaded proofing tools of LibreOffice (tried of version 4.3 and 3.3) and iSpell. I placed the files ...
0
votes
0answers
39 views

Optimizing query - understanding explain

I've got a query (partly generated by django orm, but can be modified). For some reason, It's very slow (between 1.5 -3 seconds, without having lots of data on the system) . I've got an "explain" ...
0
votes
1answer
14 views

Postgresql two tablespaces one on SSD and one on SATA III HD

Continuing my previous question PostgreSQL maximize performance SSD the DBA SE community has given me efficient advice on optimal settings for PostgreSQL on SSD. The problem is that I will define two ...
0
votes
0answers
27 views

Recursive query

I am trying to calculate the running difference between 1 row and the row before. To make it a little easier to myself I created a properly grouped representation of the data in a temp table before ...
1
vote
2answers
28 views

Postgres top-k with ties

Assume I have the following DB table with 3 integer fields. A | B | C 1 | 2 | 3 1 | 2 | 4 1 | 3 | 1 2 | 4 | 2 2 | 4 | 3 When I do: SELECT * FROM dbTable ORDER BY A,B LIMIT 1; I get 1 | 2 | 3 ...
0
votes
0answers
7 views

Creating a rule increases load disproportionally

I hope the group can forgive the vagueness of the question: In Postgres-9.3 we created a rule to be executed after an update on a table's values. The rule inserts values to another table. We tested ...
-1
votes
0answers
31 views

Display all records from a table

You can see my schema here. There are few things that I want to get but I don't know how to. In the brand_product table I have all my products associated with their brands, but as you can see in ...
0
votes
1answer
22 views

Have multiple foreign keys or one

I have 3 table: brand: brand_id(pk), name product: product_id(pk), name brand_product: brand_product_id(pk), brand_id, product_id And I have a report table, at the moment it looks like this: ...
1
vote
1answer
35 views

Linking first and second level composite primary keys

I am trying to create a new database with a hierarchical structure of two many-to-many tables using composite keys. Unfortunately the creation of tables fails stating that the foreign keys do not ...
0
votes
0answers
12 views

Bucardo doesn't do fullsync

I have a 2 computers, one of them called master, and the other called slave both using PostgreSQL 9.3.5, but has different OS (32-bit and 64-bit) I have installed and setup the bucardo on the server ...
0
votes
2answers
22 views

Best way to update the content of large lookups tables

In our application we have a about 20 tables that contain lookup information (read only data that the application doesn't modify). Nightly we'd like to update this information by deleting all the ...
0
votes
1answer
17 views

PostgreSQL 9.3 Amazon RDS gives error while using UUID-OSSP extension for generating uuid

I have created DB instance on RDS and used below function it gaves error while with same postgreSQL version on local development machine works fine. create or replace function uuid() returns uuid as ...
1
vote
1answer
28 views

Change existing column in PG to auto-incremental primary key

I have a database in Postgresql, which was migrated from SQL Server (only data). On SQL Server, a table from this database has these columns: measure_id datum measure where measure_id is ...
2
votes
1answer
40 views

(How) can I return the most specific row on a join?

I'm trying to work out a query that can parse through a table of prices (and exceptions to those prices) to yield the applicable price for a product, ideally using a join. I'm on PostgreSQL. For ...
0
votes
1answer
35 views

Postgres table contains 1 column, and that column is PK. How to insert new record?

In my application, I have an abstract base class, and 2 derived classes. The number of derived classed will probably not increase. If it does, it won't be more than a few. The class structure looks ...
-1
votes
0answers
15 views

pg upgrade saving database definition taking time

I am in process of a pg_upgrade from 8.4 to 9.3. I am using this technique: http://momjian.us/main/writings/pgsql/pg_upgrade.pdf The upgrade has been running since 250 hours, and it has been on the ...
0
votes
1answer
25 views

Postgres columns of latitude and longitude from varchar to numeric

I have a table with latitude and longitude columns and they are in varchar. I have to convert them to numeric because that is how the fields are in the master table. Is there a way to change all the ...
1
vote
1answer
35 views

Upgrading 8.1 to 9.3 - using statement log and WAL to check compatibility?

We're migrating from postgres 8.1 to 9.3, since we need streaming replication badly. All the application developers have deserted the company a long time ago, way before my time here, so there is ...
0
votes
2answers
45 views

Postgresql. Generate sets of random integers

With this command I can generate 16384 random integers between 1 AND 200,000. SELECT generate_series (1,16384),(random()*200000)::int AS id I want to generate 10 sets of such integers. Each set ...
2
votes
2answers
233 views

PostgreSQL maximize performance SSD

I will have a huge PostgreSQL 9.3 database with many tables with more than 100M entries per table. This database will be basically be read-only (once I fill all the necessary tables and build the ...
1
vote
2answers
104 views

Slow querys on billions-rows-table // index used

Since I'm a young developer and not realy skiled in using databases (PostgreSQL 9.3) i ran into some problems with a project, where i realy need help with. My project is about collecting data of ...
0
votes
0answers
11 views

when install pgpool is also needed install hearbeat and watchdog

I am trying to get the PostgreSQL services High Availability (HA) and then I am using pgpool-II. When I install and configure pgpool, the WATCHDOG section seems to doesn't works, then I have a ...
-1
votes
0answers
15 views

Error regarding SHMMNI parameter in your kernel durign the install of Postgres 9.3

During the install of Postgres 9.3 I get... FATAL: could not create shared memory segment: No space left on device DETAIL: Failed system call was shmget(key=1, size=36, 03600). HINT: This error does ...
0
votes
0answers
26 views

could not connect to pgpool server

I am configure my pgpool server to allow access from: host all all 0.0.0.0 trust The service pgpool is running: user@pgpool:~$ sudo service pgpool2 status * pgpool-II is running. ...
0
votes
0answers
29 views

Using a Virtual IP for pgpool instances doesn't work

I did a configuration of my pgpool instances, I am using a virtual ip address, delegate_IP = '192.168.1.100' <-- floating IP address ifconfig_path = '/sbin' if_up_cmd = 'ifconfig eth1:0 inet ...
0
votes
1answer
14 views

Using pgpool and PostgreSQL in differents hosts.

I want to use pgpool but I did some research and I have a doubt, if neccesary install PostgreSQL and pgpool in the same machine? When I did my first configuration and installation in a same host. But ...
0
votes
1answer
28 views

psql: Run commands in batch mode

Consider a .sql file with a few selects and updates. I would like to run this file from a script using the psql utility: psql -h whatever.rds.amazonaws.com -U user dbname -f commands.sql The ...
1
vote
1answer
15 views

Connection pooling with 3 databases but 1 database as master for repmgr replication?

I'm using pgbouncer for connection pooling against 3 database on different servers, now I'm planning to use repmgr for replication, my question is, can I keep doing connection pooling on these 3 ...
0
votes
0answers
30 views

Using Configuration Management for DBA tasks?

We have a couple of Postgres 9.3 database servers in our system, all of them with their respective config files, databases, roles, role groups, permissions on schema/tables, extensions etc. We use ...
1
vote
1answer
70 views

PostgreSQL performance worse when adding CPUs

I ran into a strange behaviour when benchmarking PostgreSQL. The setup is like this: Virtual box VM, with 3 GB of RAM. The host has 16GB of RAM and 4 CPUs. Ubuntu 14.04, standard PostgreSQL 9.3 ...
1
vote
0answers
13 views

how to get pgpool2 HA

I am using pgpool2 and PostgreSQL to get a HA of postgresql service. I have been working in this: .-----. .--------. | | W | DB | | PGP ...
0
votes
2answers
37 views

for archive_command use an script rather than a command

I want to create a configuration to get a PostgreSQL HA. In my Master I want to configure a postgresql.conf file with this: archive_command = 'local_backup_script.sh "%p" "%f"' instead of: ...
0
votes
1answer
33 views

PostgreSQL : drop column from view

I have a VIEW where I'm attempting to create an evolution script for, so I can add a column to it. That part works fine; column added just fine. However, the reverse does not work; remove that last ...
1
vote
2answers
48 views

Why wouldn't VACUUM ANALYZE clear all dead tuples?

We run a "manual" VACUUM ANALYZE VERBOSE on some of our larger tables after we do major DELETE/INSERT changes to them. This seems to work without issue although sometimes a table's VACUUM job will run ...
0
votes
1answer
32 views

Compare the same column on different dates

I have two tables project_key: project_id, key_id project_report: project_id, key_id, level, created_at I would like to compare the column level when created_at is now() and created_at is ...
1
vote
1answer
36 views

Postgres create an insert return rule

I am failing to create a rule which will return the insert's data. The table is: CREATE TABLE foo(a int, b text); My attempt for the rule is: CREATE RULE return_data AS ON INSERT TO foo DO ...
0
votes
1answer
23 views

Trying to load a file into a database on virtual machine

I have set up a Postgres db on a linux vm and have been having no issues using a GUI to connect to it. However, I am trying to load a large, 32GB, file onto it and so am skeptical of the old way I was ...
0
votes
0answers
27 views

DSN connection and RODBC on windows 8

I wish to run queries on a PostgreSQL database in R using the RODBC package. Before I had windows 7 and it was working but now trying to make it work on windows 8 I have not yet succeeded to find ...
2
votes
1answer
49 views

postgresql 9.3: two instances on same port and two different ip adresses don't work

i'm setting up multiple instances of postgres 9.3 on the same machine (centos 7). I have 2 virtual ip addresses, one instance is bound to first address and second is bound to second one. Same ...
2
votes
2answers
61 views

Trying to set up new data_directory on Postgres 9.3

Ubuntu 14.04, recently installed Postgres 9.3. Trying to set up a new data_directory to an attached, larger hard drive (/data), and I changed the postgresql.conf file to reflect that. However, I get ...
0
votes
0answers
16 views

produce a view from the output of a with query

I would like to produce a view based on few tables. So far I did this: WITH keyword_group AS( SELECT g.id AS group_id ,array_agg(pk.keyword_id) AS keyword_ids FROM group g ...
0
votes
1answer
9 views

Postgresql warning: type reference X converted to Y (SQL State: 00000 - Error Code: 0)

When I execute: CREATE VIEW foreign_keys AS SELECT tc.table_name, kcu.column_name, ccu.table_name AS foreign_table_name, ccu.column_name AS foreign_column_name FROM ...
0
votes
0answers
41 views

Loop update 1000 rows at a time

For the following table I want to loop update something like 1000 rows at a time. I have accidentally from ruby on rails application changed the updated_at timestamp so now old transactions shows up ...
1
vote
1answer
42 views

PostgreSQL db volume at 100%. Can I create a tablespace on another volume so I can vacuum full?

I have run out of space, because I am new to Postgres and I have just not been monitoring it properly. Now I cannot even vacuum the tables: ERROR: could not extend file "base/20851/11787": No space ...
0
votes
1answer
33 views

FDW, replication and CREATE SERVER - will it work?

We have two servers (master, slave) and we want to use the FDW. The two databases to be connected with the FDW are both on the master (and the slave of course). When we are to CREATE SERVER we would ...
0
votes
0answers
36 views

Suppression of statement in Postgresql logging

Given the following SQL function: CREATE OR REPLACE FUNCTION log_a_notice() RETURNS void AS $BODY$ BEGIN RAISE NOTICE 'This message is coming from log_a_notice()'; END; $BODY$ LANGUAGE plpgsql ...
1
vote
1answer
32 views

Permission for sequence in another schema

Postgres 9.3 Debian 7.0 I created a specific schema for a specific user and created a view in this schema for this user, so it's the only table he knows that exists. The problem is that this same ...
0
votes
1answer
56 views

Delete returns nothing in Psycopg2?

I have a fairly simple delete query in a PostgreSQL database that I'm interfacing with via psycopg2. Take the following minimal example: def testDelete(): db = DbInterface() cur = ...