PostgreSQL version 9.3

learn more… | top users | synonyms (2)

1
vote
1answer
15 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
21 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
15 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
51 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
27 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
41 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 ...
3
votes
1answer
32 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
20 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
22 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
43 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 ...
0
votes
0answers
10 views

Select statement to extract range of hours from Time String [migrated]

I have a PostgreSQL table like this. city_location | isp | date_ | time_ ---------------+------------------------+------------+-------------- Cambridge | Akamai ...
1
vote
1answer
54 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
13 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
18 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
10 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
29 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
17 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, ...
0
votes
0answers
16 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
13 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
13 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
36 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
53 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
60 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
41 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
77 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
36 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
83 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
72 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
13 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
19 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
30 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
66 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
15 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 ...
0
votes
1answer
25 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 ...
1
vote
1answer
26 views

Deleting aged postgresql rows with foreignkey constraints

I have a postgresql table where I want to delete rows beyond a certain age. But the catch is that some foreign key constraints would be violated For instance, if I run DELETE FROM links_reply WHERE ...
1
vote
0answers
36 views

Manual postgres migration for Django (python) app

I have a Django (python) data model called Message: class Message(models.Model): text = models.CharField(max_length=50) sending_time = models.DateTimeField(auto_now_add=True) I added sender ...
1
vote
1answer
68 views

Array of template type in PL/pgSQL function using %TYPE

I have a PostgreSQL database and a PL/pgSQL function that currently has arguments with copied types (for example IN arg_1 table_1.column_1%TYPE). I want to update it so one of those arguments is ...
1
vote
0answers
53 views

Foreign key violations detected upon UPDATE on another column

I have a table country that is referenced by many tables like so: db=# \d client Table "public.client" Column | Type | Modifiers ...
1
vote
1answer
61 views

Change AWS's Read Replica's streaming replication to trigger based replication

We have a Postgres RDS instance for which we have created a read replica instance. The problem we are facing is that, that whenever there is a long running query(about 30-40 mins) on Read Replica, it ...
1
vote
1answer
55 views

How do I move a PostgreSQL tablespace?

Is there a way to physically move a PostgreSQL 9.3 tablespace from /old/dir to /new/dir? I'd like to just mv the directory and tell PostgreSQL that the tablespace is now located at /new/dir. Looks ...
0
votes
1answer
35 views

Source of error on psql data load

Upon importing a file derived from rails establishment, psql is hitting the error of foreign key constraints: ERROR: insert or update on table "documents" violates foreign key constraint ...
2
votes
1answer
39 views

How do I recover my database after losing a tablespace with indexes?

I'm thinking of moving all the indexes in my PostgreSQL 9.3 database to a tablespace on an SSD for improved performance. This SSD is not in a RAID array and I need to consider a scenario where it ...
0
votes
0answers
42 views

move a postgres cluster to another machine

I have a postgreSQL 8.3 cluster I should move onto a new machine which runs postgres 9.3 (Ubuntu 14.04) . First of all I copied my whole cluster and I pasted into ...
0
votes
1answer
23 views

Can we use subquery in GRANT statement in postgres

I want to grant select privilege on few selected tables. Can I use sub-query in GRANT statement???? I have tried the following query: GRANT SELECT ON TABLE (SELECT array_to_string( ...
0
votes
1answer
27 views

postgis installation gone awry

[cross-posted from stack ove On a Ubuntu 14.04 staging server a MySQL connection protocol had to be installed for full-text searching for a separate application. This led to installing, uninstalling ...
0
votes
1answer
79 views

Getting records for yesterday, but per hour

I am currently getting all data for yesterday from my database but I want to get data for each hour. This is my query: select camera_id, count(*) as snapshot_count from snapshots where ...
5
votes
1answer
126 views

dumping database with “compressed data is corrupt” errors

I'm using pg_dump to dump a large (396 GB) PostgreSQL database: pg_dump --clean --create mydatabase After running for a day, a statement fails with ERROR: compressed data is corrupt and pg_dump ...
0
votes
0answers
78 views

Postgres, promoting slave to master issue

We had the following setup in place db01 -> Master Postgres 9.3 db02 -> Slave Postgres 9.3 Master data got corrupted (logically) and as soon as it was identified we shutdown the server. Dev team ...
0
votes
0answers
31 views

Variable type to store result of SELECT returning multiple rows in PostgreSQL

I'm writing a function in PostgreSQL 9.3 and I want to store the names of the columns of a table into a variable, but I'm struggling to find the correct data type: CREATE OR REPLACE FUNCTION ...