This tag is specifically for PostgreSQL version 9.1

learn more… | top users | synonyms (1)

0
votes
1answer
78 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
165 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
54 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
57 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
7k 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
3k 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
65 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
75 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
294 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
271 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
71 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
200 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
45 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
145 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
1k 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
37 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
63 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
2k 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
39 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
73 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 | ...
0
votes
1answer
187 views

PostgreSQL data directory/files portability

I have lost my PostgreSQL 9.1 installation on my Ubuntu machine by update to Ubuntu 14.10. Now I have no (reasonably easy) way to export the data from my PostgreSQL 9.1 database. Is it possible to ...
1
vote
2answers
209 views

ACID violation in Postgres 9.1?

I am using a postgres DB to implement a scheduling of jobs for a large number of computers/processes. To make the story short, every job has its id, all scheduling is implemented with three tabes: all ...
0
votes
2answers
40 views

Why Postgresql 9.2 word boundary not working!

In my postgresql version 9.1 this query is working fine: select id,name from flash_card_set where name ~* '\yACT\y' and matching: text ACT txt,ACT tests,tests ACT But in my postgresql version 9.2 ...
0
votes
1answer
3k views

How can I solve postgresql problem after deleting wal files?

I turned the archive_mode on on my postgresql configuration for testing a backup server . And since the wal files took lots of disk space, after my test I turned it off and also deleted the wal files. ...
0
votes
1answer
56 views

postgresql updating integer column taking more space than expected

I have two tables, A(a1 serial, a2 varchar(8)) and B(b2 varchar(8)); My query looks like, alter table B add column b1 integer; update B t1 set b1 = t2.a1 from (select b1, b2 from B) t2 where t1.a2 = ...
0
votes
1answer
3k 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 ...
0
votes
1answer
77 views

When do I have to use archive_command and when not

I have some doubts about PostgreSQL replication command. I am doing some research and I found severals configurations. first one is: archive_mode = on archive_command = 'cd .' second one is : cp ...
1
vote
0answers
64 views

I am not able to create/reindex a unique index in a table

I have a table that contains user locations (called public.user_location) and is the following: Column | Type | Modifiers id | ...
1
vote
0answers
197 views

pg_basebackup could not get WAL end position from server

I'm trying to pg_basebackup my database. I followed instructions in PG Mauals and set it up properly (check on other server and database), but my backup isn't ending - on end of progress it's ...
1
vote
1answer
89 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
108 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
85 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
110 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
2answers
386 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 ...
0
votes
3answers
345 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
647 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
54 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
76 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
446 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
637 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
688 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) ...
3
votes
1answer
177 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
126 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
936 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
184 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 ...
0
votes
0answers
64 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
42 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
62 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
161 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
132 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 ...