This tag is specifically for PostgreSQL version 9.1

learn more… | top users | synonyms (1)

3
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 ...
2
votes
2answers
227 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 ...
4
votes
2answers
2k 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
181 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 ...
6
votes
3answers
598 views

Issue with composite type in an UPSERT function

I have a function in PostgreSQL 9.1 called fun_test. It has a composite type as input parameter and I keep getting a casting error when I call it. CREATE OR REPLACE FUNCTION netcen.fun_test(myobj ...
0
votes
1answer
35 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
1answer
45 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
218 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
335 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
2answers
386 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
42 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
67 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
136 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
78 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 ...
1
vote
1answer
627 views

Add a column contains a photo

I installed PostgreSQL 9.1 under Ubuntu 12.10 And now I have a table called property I want to add a field that can be used to store photo(link to the photo or import a photo into a column?) But I ...
0
votes
0answers
154 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
2answers
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
0answers
49 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
37 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
56 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
95 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
95 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 ...
5
votes
2answers
3k 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 ...
4
votes
4answers
12k views

How to list all views in SQL in PostgreSQL?

How do I list all views for a database using an SQL command in PostgreSQL? I would like something similar to output of the psql \dv command, but preferably just a list of view names. e.g., SELECT ...
1
vote
1answer
366 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
112 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
157 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
1answer
54 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
179 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 ?
2
votes
2answers
149 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 ...
1
vote
0answers
73 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 ...
1
vote
1answer
1k views

Concatenation of setof type or setof record

I use Postgresql 9.1 with Ubuntu 12.04. In a plpgsql function I try to concatenate setof type returned from another function. the type pair_id_value in question is created with create type ...
9
votes
2answers
9k 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 ...
0
votes
0answers
64 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
44 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
958 views

Can't set up witness in Repmgr 2.0

I am trying to setup repmgr using the autofailover quick setup tutorial provided on Github Link But I am kind of stuck on the witness section, when trying to create the witness with repmgr -d repmgr ...
1
vote
1answer
1k 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?
2
votes
1answer
842 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
vote
0answers
146 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
votes
1answer
187 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'' '' '' ...
1
vote
0answers
160 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: ...
3
votes
1answer
71 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 ...
1
vote
0answers
189 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, ...
1
vote
1answer
63 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?
1
vote
2answers
533 views

Vacuum vs Auto Vacuum

I am trying to find answers for my below few queries which will help me in fine tuning my postgres DB. I did some googling but was not able to find answer. 1) Out of vacuum or Auto vacuum which is ...
3
votes
1answer
154 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. ...
0
votes
0answers
31 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 ...
3
votes
1answer
839 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
58 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 ...
5
votes
1answer
59 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?