Tagged Questions

PostgreSQL 9.3 : 2013 release of PostgreSQL

learn more… | top users | synonyms (1)

0
votes
1answer
23 views

PostgreSQL Auditing Tool [on hold]

Does PostgreSQL 9.3 have any auditing tool that close to Oracle Audit Vault? Audit Trigger, pgaudit or Cyan Audit look behind than Oracle Audit Vault.
1
vote
2answers
27 views

Memory usage of PostgreSQL

I'm using postgreSQL 9.3 database and I have increased the shared_buffers and effective_cache_size in the settings. Will postgreSQL always take this amount of memory or only when needed? I'm ...
1
vote
1answer
19 views

Can't run digest() on PosrgreSQL 9.3 on RDS

Following the advice given on another answer, I tried using the digest() function on PostgreSQL 9.3 on AWS RDS: devdb=> SELECT digest('blah', 'sha1'); ERROR: function digest(unknown, unknown) ...
0
votes
1answer
15 views

Postgres Grouping of Like Results

Our database is running on Postgres 9.3. We have a table called notifications. Notifications contains id, type, triggering id, source id, and some other information. What I am trying to do is select ...
0
votes
1answer
8 views

PostgreSQL + PG_Monz (Zabbix) DB size too large > 1073741824

I an new to Postgres (9.3 on Ubuntu 14.04) monitoring and I have been using Zabbix/PG_monz. I am seeing errors that my database has "too many temp bytes" and that my database is "too large". My ...
0
votes
1answer
12 views

Compare table content from different PostgreSQL 9.3 DBs

The problem How do I efficiently and idiomatically compare the content of two tables, each in a different DB? I have 3 DBs (the classic dev, stg and prod). Each DB has a few tables which should be ...
2
votes
2answers
36 views

Transactions within a Transaction

What behaviour would PostgreSQL display if for example the script below were called BEGIN; SELECT * FROM foo; INSERT INTO foo(name) VALUES ('bar'); BEGIN; <- The point of interest END; Would ...
0
votes
0answers
29 views

Combine 3 tables

I have 3 tables, they have in common row index, date, profile. These 3 tables each have partitions based on profile. I need to combine the data from those tables to produce a report at the moment I ...
0
votes
0answers
21 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
19 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
28 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
44 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
17 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
31 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
30 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
38 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
17 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
23 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
31 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
41 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
31 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
36 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
47 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
236 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
123 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
14 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
16 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
27 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
31 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
17 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
31 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
17 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
72 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
15 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
39 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
37 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
61 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
33 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
39 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
24 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
29 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
53 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
67 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 ...