PostgreSQL version 9.1

learn more… | top users | synonyms (1)

1
vote
1answer
2k views

FATAL: catalog is missing 1 attribute(s) for relid 2662

I have a server with PostgreSQL9.1-903 (Windows 7 Professional). After a power cut my database is inaccessible (I presume that the database is corrupted). The postgreSQL service starts but the ...
1
vote
1answer
2k views

Problem using pg_dump in Windows 7

I am trying to copy a remote database onto my local machine using the following command C:\Program Files\PostgreSQL\9.1\bin>pg_dump -h remote_server -U remote_user_name -c database_name | psql -a ...
2
votes
2answers
197 views

Limiting read access in postgres to few rows

We have a requirement where we want to limit the maximum no. of rows that a select query can return when its done using a particular login. So basically we would like to append a limit clause to any ...
4
votes
2answers
3k views

PgPool 2 with HA Proxy

So, I'm looking to add high availability to a postgresql server. I've looked into some tutorials on the web but, haven't been able to find exactly what I'm looking for. E.g.: PostgreSQL ...
1
vote
1answer
112 views

postgresql 9.1 failed insert occupies increasing disk space

I have a postgresql 9.1 installation where the available disk space is very low (20Gb) however there is only one client that inserts data in various tables (typically one entry every few minutes) and ...
0
votes
2answers
47 views

CASE with max()

I know how to do this in SQL Server but Postgres is different. I want to return the max employee pay. Select b.enumber, b.bday, case when Max(c.id) then c.pay ELSE c.pay End As "Current Pay" From ...
0
votes
0answers
31 views

Is there any way to reduce pg_largeobject size?

The Backup team are concerned about DB backup since it's huge and taking a long time to back up (physical backup - 300 GB - approx 5hrs). 90% of data is in pg_largeobject. I could remove loid which ...
18
votes
2answers
11k views

Configuring PostgreSQL for read performance

Our system write a lots of data (kind of Big Data system). The write performance is good enough for our needs but the read performance is really too slow. The primary key (constraint) structure is ...
12
votes
2answers
8k views

Moving postgresql data to different drive

I am using AWS as my cloud environment. I installed PostgreSQL on the same drive as my root instance volume. I have attached and mounted the second drive to my instance. Now I want to move all my ...
11
votes
1answer
14k views

How to use aes-encryption in PostgreSQL?

I tried aes-encryption by using following statement: SELECT encrypt('test', 'key', 'aes'); which worked, but I am not able to decrypt the value. I inserted it in a field of datatype bytea but I'm ...
27
votes
3answers
32k views

Optimizing queries on a range of timestamps (two columns)

I use PostgreSQL 9.1 on Ubuntu 12.04. I need to select records inside a range of time: my table time_limits has two timestamp fields and one integer property. There are additional columns in my ...
0
votes
0answers
10 views

postGIS out-of-db still has error when environment file is updated

I'm running postGIS 2.1 with a PostgreSQL 9.1.21 and am trying to get rasters back out of a database and am running into the error: ERROR: rt_raster_load_offline_data: Access to offline bands ...
21
votes
3answers
110k views

How do I insert a row which contains a foreign key?

Using PostgreSQL v9.1. I have the following tables: CREATE TABLE foo ( id BIGSERIAL NOT NULL UNIQUE PRIMARY KEY, type VARCHAR(60) NOT NULL UNIQUE ); CREATE TABLE bar ( id BIGSERIAL ...
7
votes
4answers
13k views

PostgreSQL: How to backup only One Schema from a database and restore it on another server

I have a database named "A" which has two schemas "B" and "C". I want to backup and restore Schema "B" on a different server? not sure how to do this as I am new to Postgres. Do I have to create a new ...
1
vote
2answers
115 views

Converting rows to columns

I have data like following: created_at | status ---------------------+------------- 2016-04-05 1:27:15 | info 2016-04-05 3:27:15 | info 2016-04-05 5:27:15 | warn 2016-04-05 ...
2
votes
1answer
28 views

How to count multiple values of a column as one group? [duplicate]

I have a table main with the following rows: id | name | rank ----+--------+------ 1 | Ali | a 2 | Sami | b 3 | Khan | c 4 | Kamran | d 5 | Imran | e 6 | Asad | a 7 | ...
4
votes
1answer
2k views

Postgres 9.1.6 Error index contains unexpected zero page at block 0

I have setup streaming replication on Postgres 9.1.6 running on a debian server and it's going on fine. When I try to run a query on the replica DB I get the error below: ERROR: index "...
0
votes
0answers
41 views

How to make sure vacuum is happening in postgres sql db

I have created a table with two columns and created index for the first column. Then inserted 2 million data using generate series function for first column (2nd column is having null). And executed ...
0
votes
0answers
15 views

pgadmin III backup screen freeze

I wanted to get a backup for a very large database with pgAdmin, I started as usual backups, but after a couple of hours it still runs, and I can't cancel it also. I don't even get to see the file ...
8
votes
1answer
2k views

Unexpected Seq Scan when doing query against boolean with value NULL

I have a database column called auto_review where column type is boolean. There is an index for that field, created using the ActiveRecord ORM. CREATE INDEX index_table_on_auto_renew ON table USING ...
3
votes
1answer
217 views

Error while restoring Postgres DB Dump

Using below command to restore the database Restoration command: cat my_db_dump.sql.gz | gunzip | psql -d new_database -U my_username Note: my_db_dump.sql.gz size is 4.5 GB, (extracted size will ...
2
votes
2answers
236 views

PostgreSQL - is it possible to use a plpgsql function in a bash script?

Target : What i'm trying to achieve is to restore a partial backup of a database (only certain rows that satisfied certain conditions have been backed up). Problem : Since between the partial-backup ...
9
votes
2answers
39k views

Forgotten PostgreSQL Windows password

This morning I’ve been trying to connect the PostgreSQL database on my Windows 7 Professional desktop. The default value is ‘postgres’, but sure enough I forgot what password I used when I originally ...
0
votes
1answer
184 views

Postgres-9.1 having more Idle connections

The connections were created 2 days before. Can I kill the process manually by using the below query? select pg_terminate_backend(procpid) from pg_stat_activity where datname = 'mydb' and procpid <...
1
vote
1answer
46 views

Trigger for removing group with no users left in

I have three tables: USERS, GROUPS, and GROUP_MEMBERSHIP CREATE TABLE USERS ( ID BIGSERIAL NOT NULL, NICKNAME VARCHAR(20) NOT NULL constraint USER_EXISTS UNIQUE, PRIMARY KEY (ID) ); CREATE TABLE ...
84
votes
4answers
129k views

How do I list all schemas in PostgreSQL?

When using PostgreSQL v9.1, how do I list all of the schemas using SQL? I was expecting something along the lines of: SELECT something FROM pg_blah;
2
votes
1answer
110 views

Is there some kind of table-level replication in PostgreSQL?

I work with two databases in PostgreSQL 9.1, say A and B, one per application. Database B needs to read (and not write into) one table in database A (say users), but at the same time some tables from ...
3
votes
1answer
59 views

Find current owner of custom data type?

In Postgresql, when I try to run ALTER TYPE x ADD VALUE y; on a custom datatype I get an error stating PG::Error: ERROR: must be owner of type x. I know this can be solved by running ALTER TYPE x ...
6
votes
2answers
6k views

remove password requirement for user postgres

I understand that, upon installation, PostgreSQL has no password for its db root user (postgres): postgres=# select usename, passwd is null from pg_shadow; usename | ?column? ----------+---------- ...
2
votes
2answers
55 views

How to unite 2 subqueries to reduce retrieving data from cumbersome tables?

There are 3 tables: user, conversation, message. Table user: id, login, last_login_time (time of last user's login), ... Table conversation: id, user1_id, user2_id, ...
0
votes
0answers
68 views

postgresql columns are inside quotes - “ ” and uppercase but occurs relation does not exist error when queried in java

I'm using Java and Postgresql and I've created a relation "UserTable", and when I open PgAdmin, this is how I see my database. But when I query the UserTable, in Spring Tool Suite using Java, I get ...
1
vote
2answers
26 views

trim is not working for few string

I want to trim dot (.) from a column value. Just say string is "Mr." and I need to trim the dot from the string. I am expecting "Mr" Lets say, I have two fields in my table id | value 1 ...
0
votes
0answers
69 views

autovacuum launcher process, but no autovacuum workers

Debian Squeeze (yeah, I know), postgresql 9.1. Two similar db servers, one master one shard. postgresql.conf identical between the two besides small memory footprint config differences - all vacuum/...
0
votes
0answers
13 views

log_line_prefix to starts with month in postgresql log

I have enabled the following line to analyze postgresql log in pgFouine log_line_prefix = '%t ' I am getting log output as follows 2015-12-13 04:30:36 IST [21709]: [4671-1] LOG: I guess, ...
1
vote
1answer
212 views

Postgres 'could not write to hash-join temporary file: No space left on device'

I was getting this error a while ago and I was curious about two things - 1- How do the internals of postgres work that cause this message to occur in the first place? What is the hash-join in ...
8
votes
3answers
5k views

Replication has failed; how to get going on again?

I'm running Postgres 9.1.6 on Ubuntu and I have streaming replication setup between a master and slave. Everything has been running smoothly until the database crashed and we had to restart both of ...
0
votes
1answer
41 views

Connecting existing Postrgres database with pgadmin

I have a pg database and recently I have installed pgadmin3 to view the table and data in it. After installing pgadin3, I am confused how to display my existing pg database in pgadmin3. How to link my ...
0
votes
1answer
73 views

Change postgresql data directory location

Currenly my postgresql service running with /usr/local/psql/bin/postmaster -d /usr/local/pgsql/data Due to insufficient disk space (data directory having 10GB of data), I planned to do the ...
1
vote
1answer
46 views

enabling fsync in postgresql 9.1

currently fsync option is disabled in postgresql.conf. If I am enable that, From: #fsync = on To: fsync = on (I am enabling fsync to avoid data corruption if any failure since my data (...
0
votes
0answers
13 views

Will PostgreSQL server work if I install the service in mounted partition

I have created all tablespaces (only tables created in these tablespaces) in mounted partition, But db is available in pg_default tablespace (root partition where postgresql installed). I am facing ...
2
votes
1answer
229 views

How to verify that a PostgreSQL base + WAL backup has been restored correctly

Coworkers were trying to extract a PostgreSQL database copy from a backup made at a hot standby in version 9.1, but it wasn't reliable - we would run it daily, but usually it would end up with various ...
0
votes
1answer
41 views

What is the best way to create tables for email campaigns?

I have table email_sent with fields: id user_id template_name sent_at opened clicks And now I need to save data which was send for each email. I have several campaigns with differnt data. For ...
2
votes
2answers
261 views

How to convert numeric values to text in sql for select query

I have string values like this '[123, 124]' I could trim the value using below command select trim('[123, 124]', '[]'); --returning '123, 124' as text I want to pass the value above as select * ...
1
vote
1answer
102 views

Is my postgres index being used?

I have a large PostgeSQL 9.1 database in production use. I want to free up some disk space. There is a large index, and I'm unsure if it's being used. select * from pg_stat_all_indexes where ...
0
votes
0answers
64 views

How to disable switch user option in PgAdmin

I have created read only user with following command (executed from postgres user) create user read_user with password 'read_pwd'; revoke all privileges on database db1 from public; revoke all ...
2
votes
0answers
63 views

Postgresql delete taking lots of CPU and time

I have the following SQL Query for deleting rows from my table. The query takes a long time and puts a high load on my server. delete from message_log where from_id = ? OR to_id = ? My table ...
0
votes
1answer
38 views

How to read all metadata without select permissions to actual data?

I need to create a user account that can read the metadata for all objects from information_schema I can access the information_schema with any user but only for tables that the user has select ...
47
votes
3answers
119k views

Granting access to all tables for a user

I'm a new to Postgres and trying to migrate our MySQL databases over. In MySQL I can grant SELECT, UPDATE, INSERT, DELETE privileges on a low privileged user and enable those grants to apply to all ...
20
votes
3answers
18k views

upgrading from postgres 9.1 to 9.3 on ubuntu server

I have my production server(ubuntu 13.10) running with postgresql 9.1. I want to use few features of 9.3, hence want to upgrade. Could someone help me with upgrading from 9.1 to 9.3 so that there is ...
1
vote
3answers
120 views

Why does the query plan still sort a table despite having an index sorted on the columns?

I'm using Postgres 9.1 I have two tables I am joining: wikidb=> \d page Table "public.page" Column | Type | Modifiers ---------...