This tag is specifically for PostgreSQL version 9.1

learn more… | top users | synonyms (1)

1
vote
1answer
20 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 ...
2
votes
1answer
17 views

trigger can't access column loop

SSCCE: The following script: $ cat test.sql CREATE TABLE public.foo ( loop INTEGER ); CREATE OR REPLACE FUNCTION public.foo_fun(loop INTEGER) RETURNS BOOLEAN AS $$ SELECT TRUE; $$ LANGUAGE SQL; ...
1
vote
1answer
30 views

why is the name `loop` treated differently in PostgreSQL

I issue the following DDL: CREATE TABLE foo ( loopa INTEGER, loop INTEGER ); Then, examining the DDL in the server (using a client-side tool like DbVisualizer - sorry don't know the query to ...
3
votes
2answers
41 views

idiom to treat empty result set as zero

This is obviously a SSCCE. I have an inventory table that holds the number of items (numOfItems) in a warehouse at any given date (inventoryDate). Both are integers to keep things simple: CREATE ...
2
votes
1answer
29 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 ...
2
votes
2answers
83 views

SQL order by query results in any arbitrary (but reproducible) manner

For testing purposes I need to get some data from a set of database tables in any arbitrary (but reproducible) order. The idea being that I can later compare two runs using a textual diff tool. Is ...
2
votes
2answers
76 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 ...
0
votes
3answers
34 views

postgresql cannot drop a table when autovacuum holds lock on it

Autovacuuming process holds RowExclusiveLock and ShareUpdateExclusiveLock on the table, which I want to drop. I read that autovacuum should be canceled automatically, when submitting DROP TABLE ...
0
votes
1answer
15 views

How to access pg_settings from separate connection

I am trying to access the pg_settings table from a separate connection belonging to a legacy application. I know how to identify the connection's procid from pg_stat_activity, but I am unsure how to ...
3
votes
1answer
26 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
75 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
1answer
15 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
48 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
16 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
21 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
83 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
34 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
31 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
32 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
51 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
36 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
60 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
90 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
74 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
57 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
110 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
109 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
38 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
61 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
112 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
59 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
32 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
82 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
229 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
128 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
593 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
116 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
164 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
111 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
58 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
396 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
79 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. ...
6
votes
2answers
4k 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
424 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
53 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
57 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
30 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
75 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
14 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 ...