This tag is specifically for PostgreSQL version 9.1

learn more… | top users | synonyms (1)

2
votes
3answers
1k 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 ...
0
votes
1answer
711 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 ...
2
votes
1answer
122 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 = ...
0
votes
1answer
730 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 ...
4
votes
2answers
19k 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
49 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
160 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
38 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
516 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 ...
1
vote
2answers
125 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
1k 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
444 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
1answer
28 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: ...
0
votes
1answer
660 views

Configuring Postgres 9.2 streaming replication with Redhat Clustering Suite for High Availability

I'm in the process of configuring postgresql 9.2 streaming replication with Redhat Clustering Suite for high availability. I have 1 master and 1 hot standby configured to replicate the data from ...
1
vote
1answer
50 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 ...
38
votes
3answers
62k 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
0answers
43 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 ...
18
votes
3answers
41k 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 ...
2
votes
2answers
58 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 ...
5
votes
2answers
4k 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
419 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 ...
4
votes
1answer
1k 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
11k 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
35 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
23 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
52 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
7 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 ...
11
votes
3answers
15k 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
165 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 ...
1
vote
1answer
86 views

Recommended Postgres settings to speed up indexing on a 100GB database?

I have just installed Postgres 9.1 on a server with 32GB of RAM and 320GB solid-state disk. I will be using this server to serve both my site and my database (i.e. it's not just a database server). ...
0
votes
1answer
52 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 ...
1
vote
1answer
87 views

PostgreSQL - partial dump of large orbjects (pg_largeobject table)

Context : Our web-app is like a collaboration platform where you can share files, appointments, survey and so on. An instance of this web-app is divided in communities, each one is independent to each ...
0
votes
1answer
28 views

Can an active Postgres serverlog be truncated?

Running Postgres 9.1 on Ubuntu 14.10 LTS. I've got an application running on a machine that's going to run out of diskspace soon. However, the application is in the middle of a crucial run that has ...
1
vote
0answers
34 views

Postgres configuration, incoming connections

Hello I'm trying to figure out the best way to allow connection from certain ips/hosts to my db instance. I've got an api(s) pointing to the database and right now we do something like this ...
3
votes
3answers
5k 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
1answer
2k views

How I can copy from local file to remote DB in PostgreSQL? [closed]

I am a novice in psql and need some help. How can I load a local CSV to a remote DB? I am using the following command \COPY test(user_id, product_id, value) FROM '/Users/testuser/test.tsv' ...
1
vote
1answer
32 views

Upgrade PostgreSQL 9.1 to 9.3 and server no longer starts

Technically, I didn't upgrade from 9.1 to 9.3, I built a new server with Ubuntu 14.04 and used my old 9.1 config files. PostgreSQL wouldn't start: * Starting PostgreSQL 9.3 database server * The ...
2
votes
1answer
43 views

PostgreSQL Replication delay debugging

I've got a cluster of 9.1 servers running streaming replication and using WAL-E based log shipping for when that falls behind. Generally speaking it's a pretty solid setup and does not fall behind, ...
7
votes
1answer
155 views

Adding nullable column to table costs more than 10 minutes

I have problems to add a new column on a table. I tried to run it a couple of times, but after more than 10 minutes running, I decided to cancel the query because of lock time. ALTER TABLE mytable ...
2
votes
1answer
188 views

Monitoring the Postgres query parser

As I understand it, when you send a query to Postgres like: SELECT id FROM table1 WHERE name = $1; Postgres will create a query plan. The plan will be cached for the same query in the same ...
1
vote
0answers
47 views

PostgreSQL query slow when using tsvector and ORDER BY despite having index

Running on a large database (around 1.5G) The following query takes about 70 seconds to run, SELECT x FROM DBText x WHERE x.status = 2 AND x.publishedTime < 4574590406397 AND x.service = ...
0
votes
2answers
101 views

Alter table statement waiting (for hours, on dev machine), but no locks shown

I have been trying to issue a simple: ALTER TABLE tablename ADD COLUMN id_col character varying (30) type statement on a Postgres 9.1.13 build on Debian. The application is still in private beta, so ...
1
vote
0answers
39 views

Veryfing integrity of custom order column with pure SQL

I have two tables, one stores structure (using so called parent field), the other entities that can be embeded into that structure. As certain level of structure can contain both entities and deeper ...
0
votes
1answer
99 views

how are passwords encoded in pg_shadow

How are passwords encoded in column passwd of pg_shadow? Doing a: select passwd from pg_shadow; I see values like the following: md52c92c858aa1fa12144f1dcbd4ca9c3a0 ... which, doesn't seem to ...
2
votes
1answer
395 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? ----------+---------- ...
0
votes
1answer
26 views

benefit of using [md5] over [trust] authentication for local Unix sockets

I am following a PostgreSQL installation guide that advises (subsequent to installing the database for the first time), to setup a password for the postgres user: alter role postgres password ...
1
vote
1answer
55 views

Are PostgreSQL dynamic arrays more wasteful than plain text?

Today, someone was helping me to make a function in PostgreSQL. I was wanting the function to return in arrays of 100+ geometries per two columns instead of delimited text to make it easier to ...
2
votes
2answers
1k views

Check whether empty strings are present in character-type columns

I have an application that (as part of its logic) trims strings and replaces empty strings with NULL prior to insertion in the database. I guess one way to ensure that this is enforced would be to ...
0
votes
0answers
31 views

Join does not not use index in PostgreSQL?

I have a statement that selects from two tables news and status. It seems that the index on the column status of the table news is not used. This table has about 70.000 rows and the query costs too ...
1
vote
1answer
69 views

Different kinds of pricing PostgreSQL

I would like to hear your opinion about handling different kinds of pricing. We have a product table which looks almost like this: +----+------+-----------+-----------+ | ID | Name | price_old | ...