This tag is specifically for PostgreSQL version 9.1

learn more… | top users | synonyms

3
votes
1answer
21 views

timeline 2 of the primary does not match recovery target timeline 1

I want to create a configuration with postgresql and pgpool to get height availability (HA). I want to do this: .-----. .--------. | | W | DB | | APP ...
1
vote
2answers
62 views

Why can a new user select from any table?

When I create a user in Postgres, create user test with password 'test'; The user ends up being able to connect to every database in the instance. The user can also select from any table: # psql ...
0
votes
0answers
8 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 ...
0
votes
2answers
32 views

Filling in missing dates in record set from generate_series()

Consider: with days as (select day::date from generate_series(date '2013-01-01', date '2013-01-01' + 365, interval '1 day' day) day ) select 'Inspections'::text as data_label, count(i.reporting_id) ...
0
votes
0answers
8 views

Searching ObservableCollection's items in a string

i am trying to search an observablecollection's items in a string. I try as in the below code. for (int j = 0; j < DBControl.Instance.SelectLanguagesFromDatabase().Count; j++) { if ( ...
0
votes
1answer
16 views

Will insert and update query wait while a table is being vacummed?

I want to vacuum a table in postgresql version 9.1 which will take between 10 to 12 minutes, and this needs to be done in real-time, in production use. I just want to know that if any INSERT or ...
3
votes
1answer
69 views

Very large btree index with few rows (openstreetmap gis data)

I tried to import a subset of open street map data to a Postgres database using the standard tool (osm2pgsql). I then tried to rebuild the indices on the table using plain sql (dropping them, then ...
1
vote
1answer
27 views

Is it possible to run VACUUM FULL in Postgres asynchronously?

I am working on some unused data purging scripts on Postgres 9.1. VACUUM FULL is necessary to reclaim physical space after purging. However, with my data volume (5M+ rows), it takes several minutes ...
0
votes
0answers
21 views

Starting up postgres on Ubuntu for the first time

I've installed postgres on ubuntu, created a node and now I'm trying to start up that node as per the instructions: postgres --coordinator -D /usr/local/pgsql/data -p 1281 But I get the following ...
-1
votes
1answer
26 views

how to stop the execution after n-th continues query execution

Is there any way to limit the continues query execution in postgres? I have a program to run the queries (10000+). But I need know is there any way to stop (break) the execution after exactly 10000 ...
0
votes
0answers
28 views

Slow search times with functions despite index?

I'm aware that this question might be under the guidelines of being closed, but I'm out of ideas at the moment on how to optemize this table any further. Given a have a few billion records with ...
1
vote
1answer
47 views

“Recover” data from running PostgreSQL instance; data directory was deleted

I accidentally removed data directory /var/lib/pgsql/9.1/data from running postgresql server. I guess I'm going to lose my data when server will be restarted. I'm wondering if postgresql has any ...
0
votes
0answers
31 views

listen_addresses set to '*' in postgresql.conf disappears every reboot of Ubuntu

I have set listen_addresses to '*' in /etc/postgresql/9.1/main/postgresql.conf and stopped/started Postgres 9.1 with /etc/init.d/postgresql to "reload" configs. This works fine and I can access my ...
1
vote
2answers
55 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
74 views

INSERT from a SELECT combined with a single value

I have this query that returns several rows of item_id: select item_id from properties where name like 'body'; And I have the second one that returns 1 row of tag_id: select id from tags ...
2
votes
1answer
65 views

Why is vacuum at a critical level in PostgreSQL when I'm running autovacuum?

I just checked the state of vacuum in my PostgreSQL database and it's at a critical level: $ check_postgres --action=last_vacuum POSTGRES_LAST_VACUUM CRITICAL: DB "postgres" DB: postgres TABLE: ...
1
vote
1answer
54 views

PostgreSQL Function: How to refer to individual column in record return by function

I have function that returns setoff record type. CREATE TYPE CIR_TYPE AS ( ID integer, path text, cycle boolean ); CREATE OR REPLACE FUNCTION circular_ref() RETURNS setof CIR_TYPE AS ...
0
votes
0answers
97 views

Using prepared statements in libpqxx with variable numbers arguments

I am trying to do some streamlining of a PostgreSQL database application. I am collecting data in a real-time setting using a Windows application and libpqxx. I would like to look into using ...
1
vote
0answers
95 views

Storing changes to stored procedure and triggers in PostgreSQL

I want a mechanism where I can store/track changes in stored procedure and triggers. I want to track these logs in some table. What is the best possible way ?
0
votes
1answer
37 views

group by and display contents of last row for each result

I've got some data of two objects and I want to retrieve the last row of both the objects, preferably in one query. Table structure: CREATE TABLE data ( pid serial NOT NULL, dt timestamp without ...
1
vote
0answers
60 views

Postgres SQL Database Recovery

I am recovering a corrupted database, which may not really of had problems, but the server did. The host has restored a backup of the VM image (Ubuntu 12.04 LTS), however, its 10 days old, which is ...
2
votes
2answers
101 views

High volume data aggregation

I need to aggregate incoming data and save the resulting aggregates to a table. A sample of the data is below. id,time 1967,2014-04-12 00:42:55+00 1967,2014-04-12 00:42:51+00 1972,2014-04-12 ...
0
votes
0answers
58 views

Adding additional condition significantly degrades performance

Have the following query: SELECT * FROM table_A INNER JOIN table_B ON /* conditions */ INNER JOIN table_C ON /* conditions */ INNER JOIN table_D ON /* conditions */ WHERE table_B.state = 'active' ...
0
votes
0answers
28 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 ...
1
vote
0answers
68 views

Data Corruption on base backups created with wal-e?

I have streaming replication setup on Postgresql 9.1 with two machines on AWS EC2. The master has a cron job that does a daily base backup using the wal-e tool and uploading it to Amazon's S3 storage. ...
1
vote
1answer
137 views

log_min_duration_statement setting is ignored

I am running Postgresql 9.1 on Ubuntu. Exact Postgresql version is 9.1+129ubuntu1 as my package manager shows. I have 2 databases that are actively in use and they are used from a remote server. I ...
-1
votes
1answer
110 views

Select Into Regexp_replace doesn't write changes into newly generated table

I have been using this query in postgres 9.1 in order to remove '' from existing data. Below is a sample of the data before running regexp_replace. 6'' 6'' 6'' 20'' 12'' 18'' 20'' 8'' 10'' '' '' ...
0
votes
1answer
434 views

After installing postgresql and postgres-xc with apt-get, I have psql but not postgres, initdb, pg_ctl

I used apt-get on Linux Mint to install postgresql and postgres-xc. I have access to psql, the postgres user exists, it's possible to create databases. However, the server seems to be running on ...
1
vote
0answers
90 views

how do I debug postgresql 9.1 server terminating

my /var/log/postgresql/postgresql-9.1-main.log shows 2014-03-25 10:39:30 CET HINT: In a moment you should be able to reconnect to the database and repeat your command. 2014-03-25 10:39:30 CET LOG: ...
1
vote
1answer
126 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
0answers
87 views

Reset postgresql [closed]

I have trouble: I tried to restore PG backup from CentOS on my Ubuntu computer. But had a trouble: after placing backuped Centos /var/lib/pgsql/9.1/data/ to my /var/lib/postgresql/9.1/main at Ubuntu, ...
3
votes
1answer
56 views

restarting postgresql quickly

I'm reading over the postgresql admin cookbook. Some of the database server parameters require a restart and with a busy database, you may need to restart quickly. There are a number of things to do ...
0
votes
1answer
269 views

Postgresql restore data from file [duplicate]

I have a tar archive with full backup of my CentOS filesystem with pg9.1 installed. How can I restore one database from it?
3
votes
1answer
74 views

Use of integer instead of interval (of one type)

Our DB design presently has a interval column which will only be storing days (no other interval type) so it is making sense to use INT2 (smallint) instead of interval. Reference to documentation. ...
5
votes
2answers
3k 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 ...
2
votes
1answer
321 views

Debugging why postgres is snowballing with system cpu usage

I am having an issue where my postgres database which run on a dedicated machine is getting hammered with kernal level cpu usage. This essentially takes down the web service that run on this database. ...
1
vote
1answer
52 views

PostgresSQL authentication error

I recently forgot my PostgreSQL 9.1 password on my windows 8 and it asks for password on pgAdmin III console. Can I retrieve the password or reset it?
5
votes
1answer
56 views

How to see amount of reusable space for a table

I had a huge table(about 300mil rows). After massive DELETE and VACCUUM table files have stopped to grow. That's exactly what I expected. But is there any way to see how much space became reusable?
0
votes
0answers
29 views

Changed password on Postgres but I still can log in with the old one [closed]

I changed my password via alter user, but can still log in with the old one. In the 9.1.6 version of postgresql I resolved this problem by killing all the processes through pg_terminate_backend, but ...
0
votes
0answers
65 views

What is the best way to design CMDB application

Currently I am developing an Ruby On Rails application in CMDB domain and I am not sure about all the properties (columns) of an component(table) so I am designing a DB with the properties which ...
0
votes
0answers
13 views

Failure Restoring PostgreSQL database

I have a backup of my database as a .backup file that I made via pgAdminIII. The backed up database has been accidentally deleted so I have to use this .backup file to restore it. I am only praying ...
0
votes
1answer
539 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
342 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 ...
1
vote
0answers
33 views

Pg_restore table size differences

What would account for the difference in table sizes between one PostgreSQL database and another restored from its dump file? On an Ubuntu 12.04 host, I'm running PostgreSQL 9.1, and I dumped my ...
0
votes
2answers
808 views

Postgres client unable to detect UTF-8 database

Context I am deploying a Vagrant box for my development team, and have encountered a problem restoring UTF-8 collated dumps. I don't believe it is related to the now-infamous "UTF8" has no equivalent ...
0
votes
2answers
72 views

How to change table/indexes structure for a query with two aggregated colums

Do you have any suggestion on how to improve a table structure or its indexes in order to have a single query run faster? The table has this structure: docid | character varying(17) | ...
1
vote
1answer
49 views

RIGHT() function in postgresql 9.1 not behaving as anticipated?

The following is with regard to PostgresQL 9.1 : I am using the right() and left() string functions to fix a few instances where data was input into my database being “off by one” .. so I’m doing ...
0
votes
1answer
55 views

Rotate Postgres log at 5GB

How to make postgres 9.1 log to have maximum size 5GB and to be circle when comes to 5GB ? At the moment my log file is over 500GB. /var/lib/postgres/
0
votes
2answers
87 views

Big table size in PostgreSQL while number of rows is small

Tables' size in the PostgreSQL DB are big while the number of rows is relatively small (less then 500K rows). Vacuum of the specific table did not solve the problem. Only by deleting and importing the ...
1
vote
0answers
159 views

Correct pg_hba.conf to allow script to run locally with a specific db-user

I've got a working postgreSQL database with following pg_hba.conf: local all postgres peer local all all ...