PostgreSQL version 9.3

learn more… | top users | synonyms (2)

3
votes
1answer
44 views

Slow query on primary server runs fast on hot standby

I have a primary PostgreSQL 9.3.4 server with 64GB of RAM that is replicated using streaming replication to a hot standby server with 32GB of RAM. My problem is as follows: I've detected a query that ...
0
votes
2answers
27 views

Postgresql pg_hba.conf changes are ignored or not loaded

Using Postgresql 9.3 on Ubuntu 14.04.4 LTS I've been changing the /etc/postgresql/9.3/main/pg_hba.conf config file madly trying to make a change that will allow me to log in with any user remotely. ...
0
votes
0answers
26 views

Postgres cluster data on large rapidly changing table

I have a table in Postgres which will store statistical information on a range of metrics on a number of machines. Each record is fairly simple, with a date, machine ID, metric ID, and a value. Once ...
0
votes
0answers
8 views

Is it possible to convert enums to text in posgresql

Is is possible to convert an enum declared in a postgresql schema to text so that I could use like clause for the enum column?
0
votes
1answer
28 views

Use system timestamp comparison in SELECT query

I have to backup some tables on a daily basis using the condition that the backup will be only for one previous day. I am planning to use copy to dump the records in to a csv file. I need help in ...
0
votes
0answers
23 views

pg_controldata 'Time of latest checkpoint' on standby doesn't change

About 2 weeks ago, pg 9.3 master space filled up, didn't die, continued operation after space was freed up, and it's standby has kept up with it ever since. However the standby pg_xlog keeps filling ...
1
vote
2answers
31 views

Routinely deleting aged postgresql rows via cron

I have a Django website with a postgresql 9.3.10 backend. The website contains dynamic user-generated content (think of it as a form of 9gag). There are some tables from which I routinely delete aged ...
3
votes
1answer
39 views

Postgresql tables seem duplicated (with suffixes such us pkey, hashes, etc)

due to some problems I came to check the state of my current database in production and I found that some tables are "duplicated". By duplicated I mean that there appear to be some clones of the ...
1
vote
0answers
25 views

Check Postgres Listener is up/down?

I am using PostgreSQL LISTEN/NOTIFY functionality in Java. So I have made a ListenerBean which is run at Application startup. LISTENER class in java: @Singleton @Startup ...
0
votes
0answers
27 views

Force Postgres to write log to a new file

I've updated the value of log_line_prefix, and I wanted that the log file will contain only logs with the new prefix. I deleted the log file (postgresql-9.3-main.log), assuming that once the file is ...
0
votes
0answers
14 views

Pgpool2 connection pooling

I have installed both PGPOOL2 3.3 and Postgresql 9.3 db(master) on the same server and in the pgpool.conf file have given the connection parameters as: num_init_children = 100 max_pool = 2 Therefore ...
3
votes
1answer
54 views

PostgreSQL CAST() giving unexpected results

I can't seem to figure out how this is rounding overflow digits. It is most definitely not any of: Round up if last digit is 5 or higher. Round to even if last digit is 5. Randomly round up or down ...
1
vote
1answer
29 views

password confusion in postgres sql

I have logged into postgresql server using sudo -u postgres psql Then I also created a database from within /usr/local/bin directory using the command. createdb -h localhost -p 5432 -U postgres ...
1
vote
1answer
15 views

How do I restrict PostGIS database with user specific access?

I am using open source tools and working on QGIS 2.8 and Postgres/PostGIS 9.3 . Working on urban application so we have single database and many users .I want to create department wise user group so ...
2
votes
1answer
66 views

Placing a uniqueness constraint on a date range

I have a table reservation with columns roomno(INTEGER), startdate(DATE), enddate(DATE) with a primary key on (roomno, startdate). How do I place a constraint on the table such that bookings are not ...
0
votes
0answers
32 views

Considerations to take into account for moving a big table into another database

In our application, we a have 67 GB table ( very big compared to the rest of our tables ) and it acts almost as an archive table since its record are not modified and the ratio of read operations is ...
1
vote
1answer
17 views

What run-time configuration parameters are used for concurrent index creation

Suppose I were to run the following. The first line shows the server's default maintenance_work_mem value. mhildreth=# show maintenance_work_mem; maintenance_work_mem ---------------------- 16MB (1 ...
0
votes
0answers
53 views

Postgresql restore database from file encoding error

I have problem while i import database from backup I gives many output and many "invalid command" messages. In the end it gives an encoding error. what can the reason be? my command: sudo -u ...
1
vote
0answers
26 views

How can I upgrade PostgreSQL while using PgQ queue system?

I'm using PostgreSQL 9.3 and PgQ 3.2.6 for about two years and now it's time to upgrade. I found out that upgrading PostgreSQL is fairy simple. Just install new Postgres version and then: sudo ...
2
votes
2answers
80 views

best way to avoid redundant aggregate functions and/or group by columns

Suppose I have two tables: Foo: id baz Bar: id foo_id boom So a Foo has many Bars. I frequently find myself in situations where I need to compute an aggregate across the Bars for a given set ...
1
vote
0answers
32 views

PostgreSQL 9.3 Query Design Optimisation - Improving speed

I am looking to optimise a query that is currently taking ~2500ms to run in PostgreSQL 9.3. The reason I need to optimise it is that may have to be run frequently and it directly impacts user ...
0
votes
0answers
11 views

SQL query for setting value

I found this example for configuration databse tables: http://stackoverflow.com/questions/10204902/database-design-for-user-settings Can you show me some example for SQL query for getting ...
0
votes
0answers
189 views

Postgres wont install on windows 7 64 bit (Failed to load SQL modules into table)

I have been trying to install standard postgres 9.3 onto a windows 7 Machine. every single time it gives the same error: Failed to load SQL modules into table I have tried (from this question http:/...
5
votes
1answer
48 views

What permissions are required to return rows from information_schema.schemata?

In postgres (I'm on 9.3.4) what permissions are required to return rows for select * from information_schema.schemata The 9.3 documentation says "are owned by a currently enabled role". Does that ...
0
votes
0answers
21 views

Postgresql: find out all columns that have particular string and count the number

I want to create a function like this: enter table name and a particular text, then return a table that have 3 columns the : table name , column name, the number of that column have the particular ...
0
votes
1answer
26 views

Postgresql maintainenace queries fail with foreignkey violation

I maintain a Django app with a postgresql backend, where users post interesting URLs (links) and then either leave replies under these or upvote/downvote them. All this is done via 4 postgresql tables ...
1
vote
0answers
45 views

My simple FK constraints appear to have been ignored. How?

I have two tables, device and platform where device.platform_id enforces the following constraint: Foreign-key constraints: "device_platform_id_fkey" FOREIGN KEY (platform_id) REFERENCES platform(...
1
vote
1answer
67 views

PostgreSQL 9.3: Dump a database excluding the data of some tables

Consider a production database with a few dozens of normal-size tables, and a handful of huge tables. I am looking for a convenient way to pg_dump the database content, excluding the few huge tables, ...
0
votes
0answers
16 views

How to upgrade PostgreSQL 7.3 to 9.3?

I want to upgrade the PostgreSQL database from 7.3 to 9.3. Please help me how to perform the upgrade.
0
votes
0answers
44 views

Order by in json field in Postgres and using django

I am using django 1.9 with Postgres 9.3. I am executing a query to search from json data, but the problem I am facing is that I can't use ORDER BY clause if I am using JSON. The json is: { "...
2
votes
0answers
19 views

In PostgreSQL, can you read from an index during a ALTER INDEX … SET TABLESPACE operation?

I have a PostgreSQL 9.3 server, and there's a large index that I want to move to another tablespace on another disk, because I want to free up space on the original disk. However this is a production ...
1
vote
1answer
34 views

Tricky condition for PostgreSQL UPDATE query

I need help with a PostgreSQL query. I have a Django app + pg backend, where users post interesting URLs (or links) and can then comment under such links (called replies). There are two pg tables to ...
1
vote
1answer
33 views

PostgreSQL 9.3: Aggregate collection of JSON arrays

I have a table inventory with a value column that contains JSON strings, with the following data structure: { "category": { "item": ["price", "quantity"] }, "widgets": { "foo": [300, 15]...
0
votes
0answers
21 views

Diagnosing why postgresql query is going in a seemingly endless processing loop

I maintain a social forum built with Django and postgresql 9.3.10. There's a maintenance query I run via psql, to clean up my postgresql table. It goes like this: begin; DELETE FROM links_seen WHERE ...
0
votes
0answers
16 views

Remedying psycopg2:OperationalError in postgresql 9.3.10 database

I have a Django app + postgresql 9.3 DB, hosted on Ubuntu VM. Recently my VM ran out of disk space, resulting in no space left on device errors showing up in my logs. I cleared the space on the device ...
0
votes
1answer
53 views

Postgresql error: failed to re-find parent key in index

I haven't seen this before and am stumped. I have a Django app with a postgresql 9.3 DB (hosted on Ubuntu VM), and very recently I VM ran out of disk space (resulting in no space left on device ...
1
vote
1answer
48 views

postgresql: PANIC: cannot operate with inconsistent data

I'm trying to setup a replica using repmgr: repmgr -D /var/lib/postgresql/9.3/main -p 5432 -U repmgr -R postgres \ --verbose standby clone psql.master.example.com repmgr --verbose standby register ...
0
votes
1answer
84 views

Run script on multiple schemas on PostgreSQL DB

I need to run a huge migration script on multiple schemas on PostgreSQL 9.3 server. I want output to be in a .txt format if this failed on any schema. Would you be able to help me on this? Details: ...
3
votes
3answers
68 views

Create unique sequence per set of rows sharing the same foreign key value

In my system, I want each user to have an own id sequence for his articles. So, I have created a database that has a table that depicts a correspondence between users and their articles. Here is the ...
2
votes
0answers
42 views

Order by index unexpectedly slowing query down

I have two tables: houses has ~415,000 records, and account_houses has ~525,000 records. I've changed the table names for security reasons, but basically I am using this query: SELECT "houses".* FROM ...
3
votes
1answer
99 views

DELETE rows which are not referenced in other table

I have two tables in a PostgreSQL 9.3 database: Table link_reply has a foreign key named which_group pointing to table link_group. I want to delete all rows from link_group where no related row in ...
1
vote
1answer
37 views

Deleting aged postgresql rows with tricky condition

I have two postgresql tables I need to clean up as part of maintenance. The tables, as described in my Django front-end, are: class Group(models.Model): owner = models.ForeignKey(User) unique ...
3
votes
2answers
85 views

Database model about libraries

I need to make database model about libraries. In that model I'm tracking more libraries and every library has it's own moderators. Every moderator can make categories of the books and than add books ...
0
votes
2answers
184 views

Deleting old postgresql log files (Ubuntu)

In a Ubuntu machine hosting a postgresql database, I have 16G space taken up at /var/lib/postgresql/9.3/main/pg_log. Apparently, log files from many months ago are being stored as well. I manually ...
1
vote
1answer
19 views

Is there a limit to the number of database connections from a single machine in PostgreSQL?

We are planning on setting up multiple front ends behind a load balancer. I think our database can handle the load because I'm currently measuring a database TPS of about 450 for approximately ...
0
votes
1answer
24 views

Export roles *with* privileges under PostgreSQL 9.3

New to backups under PostgreSQL. I've been backing up my database with the general commands, and it works well. Except no users or privileges are stored with it. I understand that I need to do that ...
1
vote
1answer
52 views

Cannot log in with default user on Mac 10.10

I am running PostgreSQL on a Mac 10.10 PostgreSQL 9.3.5 on x86_64-apple-darwin12.5.0, compiled by Apple LLVM version 5.1 (clang-503.0.40) (based on LLVM 3.4svn), 64-bit I am having trouble ...
0
votes
1answer
74 views

Store same sequence value in two columns

I have a table in which one column is auto increment sequence number. I want the same value to be stored in another column as well (so it can be changed later). My table structure is like this: ...
0
votes
1answer
20 views

Extract date and time separately from a time with time zone column [closed]

I have a table in PostgreSQL 9.3 with the following column ...., created_at time with time zone NOT NULL DEFAULT now(), ..., When I do a select created_at from trails; I get this "02:32:44.62054-...
0
votes
1answer
26 views

Deleting aged rows from postgresql table which don't have related entries in another table

I maintain a website where users post interesting links, and other users leave replies under the said links. I want to delete aged link entries under which no one replied. The postgresql database is ...