This tag is specifically for PostgreSQL version 9.1

learn more… | top users | synonyms (1)

1
vote
2answers
146 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 ...
1
vote
1answer
16 views

Can I do VACUUM FULL to pg_largeobject table?

I have two tables (table1, table2) in a Postgres 9.1 database. Both having oid type. Each table 1 million records. And pg_largeobject table size is around 40GB. I have removed 0.9 million records from ...
0
votes
1answer
13 views

Any gotchas with changing IP address of Postgres Master?

We have to replace our Master Postgres database and due to restrictions in AWS, we cannot use the same IP address for the new server (not 100% true, but cannot do this until the original is terminated ...
0
votes
1answer
47 views

Trigger function using current row and current table name as variables

Like detailed in my first question I have multiple tables with identical layout in a Postgres 9.1 DB. They only vary in their column values: tbl_log_a tbl_log_b tbl_log_c ... 26 tables (from a to ...
0
votes
1answer
903 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 ...
1
vote
1answer
45 views

Trigger function using current row and current table name as variables (final part)

Like detailed in my first question I have a Postgres 9.1 database with multiple tables that have the exact same column names, they only vary in their column values: tbl_log_a tbl_log_b tbl_log_c ... ...
0
votes
1answer
899 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
0answers
26 views

PostgreSQL trigram GIST vs. GIN indexes

I have a PostgreSQL 9.1 database that contains 10M+ rows and some text fields that need similarity and %word%-like searches, so I decided to use trigram indexes. Initially, I started to use GIN ...
2
votes
2answers
29 views

postgresql trigram search gets much slower as search string gets longer

In a postgresql-9.1 database, I have a table table1 with ~1.5M rows and a colum label that is trigram-indexed (actually, the trgm is on lower(unaccent(label)) (unaccent is made immutable for the index ...
1
vote
1answer
38 views

pg_restore: [archiver] did not find magic string in file header

I'm using PostgreSQL 9.1 and want to restore backup files generated with pg_dump: sudo pg_dump -h 127.0.0.1 -U postgres --clean --inserts -E UTF8 -f out.sql database_name This command generates a ...
13
votes
2answers
13k 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 ...
6
votes
3answers
119 views

Can PostgreSQL use nulls in its indexes?

I've been reading this book which says that The database assumes that Indexed_Col IS NOT NULL covers too large a range to be useful, so the database will not drive to an index from this ...
1
vote
1answer
31 views

Postgresql - Point in Time recovery using recovery.conf and trigger. How to minimize the time waiting for .history files

Following http://www.postgresql.org/docs/9.1/static/continuous-archiving.html, I copy the WAL archives to another machine and apply them regularly. Recently due to pg server crash at my main server I ...
2
votes
1answer
1k views

Create database on new partition

I use postgresql 9.1, on ubuntu 12.04. I had installed the depot package. I have added a new partition to my system, and I would like to create a postgresql database on this partition ( which will be ...
5
votes
2answers
4k views

How do I list all tables in all schemas owned by the current user in Postgresql?

I can list all tables in all schemas using > \dt *.* but that also lists system tables that greatly outnumber my tables that I care about. I'd like all the tables (and possibly views) created by ...
1
vote
1answer
16 views

Use of smallserial gives error with PostgreSQL

I know the very basics of databases. I worked in the past with MySQL and recently switched to PostgreSQL (9.1) with PostGIS 2.0.1. At the moment I'm designing a new database and I use the tool ...
2
votes
1answer
2k 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 ...
0
votes
1answer
827 views

pgpool2 setup on master-slave replication using postgreql 9.1

I have already setup 2 ubuntu server 12.04 LTS on my local machine using virtual box and is using the master-slave replication (postgresql 9.1). 192.168.0.109 = master 192.168.0.110 = slave my ...
10
votes
3answers
70k 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 ...
2
votes
1answer
41 views

How to validate column while creating Table?

Need to validate the column while creating the tables in postgres , An example before developer create table We are in need to validate the Audit columns, Example : create table tab_1 ( a integer, b ...
2
votes
0answers
28 views

pg_dump with ssl certificates

I would like to run the pg_dump command from a remote host off my database server that only allows fully verified certificate based connections. How should I go about doing this? The man pages for 9.1 ...
1
vote
1answer
109 views

Will insert and update wait while a table is being vacuumed?

I want to vacuum a table in PostgreSQL version 9.1 which will take between 10 to 12 minutes. This needs to be done in real-time, in production use. I just want to know that if any INSERT or UPDATE ...
24
votes
3answers
56k 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 ...
1
vote
1answer
56 views

Connecting to a remote Postgres 9.1 DB

I am running Postgres 9.1 on Ubuntu. I have host all all my.ip.address/32 md5 set in pg_hba.conf and listen_addresses= '*' set in postgresql.conf. Running show listen_addresses shows '*' Running sudo ...
2
votes
1answer
35 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 ...
3
votes
1answer
1k 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 ...
5
votes
2answers
6k views

Disable all constraints and table checks while restoring a dump

I've obtained a dump of my PostgreSQL database with: pg_dump -U user-name -d db-name -f dumpfile which I then proceed to restore in another database with: psql X -U postgres -d db-name-b -f ...
2
votes
1answer
645 views

Trouble with installing additional modules on Postgresql 9.1 on ALT-Linux

I'm trying to create extension under postgresql 9.1 (Linux): CREATE EXTENSION pg_trgm; but got error: ERROR: could not open extension control file "/usr/share/pgsql/extension/pg_trgm.control": No ...
3
votes
1answer
267 views

Problems restoring backup from base backup + WAL with WAL-E

I'm trying to do nightly restores of the main database on a separate node. I want to do weekly full restores and nightly restores by consuming the latest WAL files. On the master I have wal_level = ...
1
vote
1answer
34 views

Moving postresql 9.1 database - live

This question has probably been asked and answered before, and if so, I would appreciate if that could be pointed out to me. I have googled this point quite a bit, but I want to be very sure as to ...
2
votes
3answers
2k views

How can I create readonly user for backups in PostgreSQL?

Is it true that it is IMPOSSIBLE to create a readonly backup user in PostgreSQL? I've been advised on an IRC channel that you simply can't have a backup only user with no ownership privileges. I ...
4
votes
2answers
24k 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 ...
2
votes
1answer
68 views

Was using `set datcollate='C'` a mistake?

In a pre-production environment running django 1.6 on PostgreSQL 9.1 we had a problem with database records arriving in a different order from what the application was expecting. So we ran this ...
3
votes
2answers
172 views

Have: Postgres database files. Need: Functioning database

I am running Ubuntu 14.04 with a postgres/postgis setup and in a weak moment removed PostgreSQL 9.1 (seemingly a legacy install from 12.04 from before I upgraded to 14.04), having erroneously ...
0
votes
1answer
134 views

Postgres automatic insert time stamp with time zone (india) [closed]

Is there any way to insert timestamp with zone automatically when query is inserted ? (just like id auto increment as i don't want to pass the the parameters in sql as time its same every where if we ...
-2
votes
1answer
34 views

Replace date function call with its functionality [closed]

I have zero knowledge of postgresql somehow I have to extract data from a postgresql database I am trying to replace function call in a query with its functionality. Function: -- Function: ...
1
vote
1answer
130 views

Postgres - copy table INCLUDING CONSTRAINT doesn't include constraint

How come that statement don't copy constraints if I explicitly ask for it? CREATE TABLE backup_schema.permissions (LIKE public.permissions INCLUDING DEFAULTS INCLUDING CONSTRAINTS INCLUDING INDEXES ...
47
votes
3answers
77k 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;
3
votes
0answers
144 views

Postgres Streaming Replication: How to re-sync data in master (with standby) after failover

I am absolutely new to postgres 9.1 replication mechanisms. I have managed to set up streaming replication between a master and standby postgres (Both have x number of records). At one point when ...
2
votes
2answers
172 views

Postgres Slow Queries - Autovacuum frequency

We've noticed the performance of our platform drop in recent weeks so I've run the following: select relname, last_vacuum, last_autovacuum, last_analyze, last_autoanalyze from pg_stat_user_tables ...
8
votes
2answers
6k 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 ...
0
votes
2answers
493 views

What is the default size of text field in Postgres?

In the official documentation (http://www.postgresql.org/docs/9.1/static/datatype-character.html), it states text field is variable unlimited length. I wonder what is the default size of this field ...
6
votes
1answer
2k views

restoring Postgres database: pg_restore -vs- just using psql

I am dumping my Postgres database using pg_dump (in plain-text format) and then restore it simply by using psql (with the -f option). Which begs the question: am I missing anything by not using ...
6
votes
3answers
13k views

PostgreSQL: changing password for a user is not working

I installed PostgreSQL on EC2 machine and now I want to change the password of user postgres I do $ sudo -u postgres psql psql (9.1.5) Type "help" for help. postgres=# ALTER USER postgres WITH ...
1
vote
2answers
89 views

What to do with WAL files for Postgres Slave reset

So last night our PG Slave ran out of space after a lot of reconfiguring of disk space, new drives etc its now reporting the following error: FATAL: could not receive data from WAL stream: FATAL: ...
1
vote
1answer
47 views

How to cancel query as non-superuser?

I'm a non-superuser on a PostgreSQL 9.1 database, and I've just created a bad trigger function that won't stop. I can see the process, e.g.: thedb=> SELECT * FROM pg_stat_activity WHERE procpid = ...
0
votes
0answers
128 views

Couldn't setup postgresql cluster with heartbeat pgsql ra

I'm trying to install postgresql cluster with the following configuration: 2 virtual hosts with RHEL 6.6 x64: postgresql 9.1 with hot standby replication pacemaker 1.1.12 resource-agents 3.9.6 I ...
1
vote
0answers
15 views

Error on slave2 after promoting slave1 to master in cascaded streaming replication

I've a setup like MASTER -> SLAVE1 -> SLAVE2. MASTER and SLAVE2 are running on different ports on single server. SLAVE1 in on another server. For testing purpose I've powered off my first server ...
13
votes
3answers
20k views

Optimizing queries on a range of timestamps (two columns)

I use postgresql-9.1 with ubuntu 12.04. I need to select records inside a range of time: my table time_limits has two timestamp fields and one property integer. Indeed there are other info columns ...
2
votes
1answer
177 views

PostgreSQL's Query Hierarchical data poor performance

Badly need some technical assistance on this query performance issue I'm working on. Summary of the data structure is: 1 project has many homeprofiles, 1 homeprofile has many tasks, 1 task has many ...