This tag is specifically for PostgreSQL version 9.1
0
votes
1answer
12 views
Postgresql: No data left on drive, how can I wipe it and start over?
I have a system with a non-critical postgres database that was unmonitored and consumed all available space on its drive. The OS is unaffected so I am free to delete files.
I would like to just ...
1
vote
1answer
22 views
How to change the default delimiter in PostgreSQL 9.1.9 using SET?
From within a bash script I tried to use a replace and a detection on a field containing a pipe like:
IF position ('|' in var) IS NOT NULL THEN ...
...REPLACE(field, '|', '#')
The data itself was ...
5
votes
2answers
157 views
+100
How to do incremental/differential backup every hour in Postgres 9.1?
Trying to do an hourly hot incremental backup of a single postgres server to s3.
I have the following setup in postgresql.conf:
max_wal_senders=2
wal_level=archive
archive_mode=on
...
6
votes
1answer
109 views
Convert units of measurement
Looking to calculate the most suitable unit of measurement for a list of substances where the substances are given in differing (but compatible) unit volumes.
Unit Conversion Table
The unit ...
0
votes
1answer
20 views
pg_upgrade fails with lc_ctype cluster values do not match
I'm upgrading my PostgreSQL version 9.1.4 database to version 9.2.4. Both the old and the new version are the bundled versions of postgresapp.com for Mac OS X.
When trying to upgrade the database I ...
0
votes
1answer
42 views
Cannot connect to server. Too many symbolic links. Postgres Error
I get this error when I try connecting to a PostgreSQL server:
psql: could not connect to server: Too many levels of symbolic links
Is the server running locally and accepting
connections on ...
0
votes
1answer
38 views
How to convert Postgres from 32 bit to 64 bit
I would like to convert from PG 32 bit to 64 bit. I am testing with pg_upgrade (in several scenarios) but I think it cannot do that.
With pg_upgrade, I can upgrade PG version 8.5 to v.9 (both of ...
1
vote
2answers
55 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 ...
0
votes
0answers
30 views
Recovering Postgres database
OK. I'm in a bit of trouble.
I've got an Ubuntu, had Postgres 9.1 installed.
There was something wrong with it - I couldn't start it for some reason. Out of panic, I backed up ...
2
votes
1answer
49 views
delete rows in 3 tables with on delete cascade
For a relation 0..n between two tables (t1,t2), the mpd generates an intermediate table (t3) with two fk.
Let two constraints on the t3 table, each one with "on delete cascade", if I do:
delete ...
0
votes
1answer
72 views
Full text search on multiple tables in PostgreSQL
I have several tables that have related data. I want to have a search input box on the dashboard of the system that will search for anything (client, invoice, delivery note, ...).
Is it possible to ...
0
votes
1answer
30 views
Keeping version history of functions in PostgreSQL
For many of my tables, I've added auditing triggers (based on this wiki page). It works very well and has helped me several times figure out who changed what in the system. We have a Python/Django ...
0
votes
0answers
53 views
Waiting for response from Postgres server
I have a problem when query from application (Java) to PostgreSQL.
Here my query list (in 1 session user):
select count (id) from tb_a;
select count (id) from tb_a;
select nextval ('cssq_01');
select ...
1
vote
3answers
73 views
Postgres connection access denied on IPv6 address
Installed PostgreSQL 9.1 x64 on Windows, set up a listen address, but when connecting with pgAdmin I get the following error. Not sure why PostgreSQL is seeing my IPv6 address and not my regular IP ...
0
votes
2answers
62 views
Postgres wont shutdown due to wal archiving
I commanded Postgres to shutdown using the init.d scripts (Linux) over 18h ago.
I can still see the processes running:
-bash-3.2$ ps -fe | grep postg
postgres 2299 3265 0 16:06 pts/5 00:00:00 ...
0
votes
1answer
36 views
PostgreSQL insert into table (not origin) based on a condition on fields on different tables
Lets say you have 4 different tables:
table_1 is a table holding the original data:
table_1
id1 | id2 | score1 | score2 | name_1 | name_2
...
5
votes
1answer
128 views
Algorithm for finding the longest prefix
I have two tables.
First one is a table with prefixes
code name price
343 ek1 10
3435 nt 4
3432 ek2 2
Second is call records with phone numbers
number time
834353212 10
...
3
votes
2answers
75 views
Change built-in default privileges in PostgreSQL?
Introduction.
When I create a database,
postgres=# CREATE DATABASE test2 OWNER test2;
it is created with an empty privileges column:
Name | Owner | Encoding | Collate | Ctype | ...
0
votes
1answer
48 views
Postgres backup and WAL to S3
We are looking for a solution for the following problem:
We have set up streaming replication so we have a master DB and a slave DB, we want to have basebackups and WAL files sent to our S3 storage ...
0
votes
0answers
37 views
How to make continues cluster?
I have a report table with the following index:
providerid, date
The table is around 30M records and it grows about 100K rows per day.
I want to use in the index above as a cluster, but as I ...
3
votes
2answers
236 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 ...
1
vote
0answers
47 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 ...
0
votes
0answers
25 views
Streaming replication in postgresql 9.1 — sender and receiver processes aren't running
I've followed this guide, and cross-referenced the primary postgresql wiki in order to set up replication. I've configured postgresql.conf and pg_hba.conf as directed, and then restarted the master ...
3
votes
0answers
51 views
What happen when Postgresql tablespace is null?
I'm facing a DWH on postgresql with no DBA (I'm not one) so I turn to you with hope to figure this issue/s.
Long story short, when I check the disk usage per tablespace, I get different distribution ...
-2
votes
1answer
134 views
Use select query in stored procedure
I have some questions about stored procedures.
Using select query inside the stored procedure is efficent or using select query in front end. Which is take less time and give the result is fast or if ...
1
vote
1answer
38 views
How to monitor or do vacuum without stop all?
I have postgres9.1
I try to run full vacuum on a specific table , but it run for more than 5 hours.
I stop all process against this table and i cannot have the process down any longer.
Is there a ...
0
votes
1answer
64 views
createdb command error : could not connect to database [closed]
I am using the following command for creating database in postgresql.
createdb -D pg_default -E UTF8 -h localhost -p 5432 -U pramil -W pramil mydb
but this command results prompt for password.When ...
1
vote
2answers
72 views
Group Database Entries by time difference
I have a large postgreSQL database with log data. All this data has timestamps and I want to group consecutive rows where the difference between the timestamps is less then 1500 miliseconds for ...
0
votes
1answer
37 views
Storing no of players the last seven days in an online game
I want to plot the number of players, in the last seven days, of an MMORPG which haves 50~ servers. The system fetches the number of players in each server every 15 minutes and I'm not really sure how ...
0
votes
1answer
43 views
Cannot create PostgreSQL user
I'm using PostrgreSQL 9.1.9 on Ubuntu 13.04.
Using the following StackOverflow question, I tried to create a user/role in postgressql:
...
1
vote
0answers
45 views
Failover - automatic switching between servers in Postgresql 9.1
I'm pretty new to Postgresql 9.1 and failover replication. We have a simple master-slave setup, for failover replication, which our application (client) is connected to. On a failure of the master, ...
2
votes
1answer
217 views
PostgreSQL: Cannot change directory to /root
I am trying to copy a table planet_osm_polygon from one database osm to another test. I su postgres and performed the pg_dump.
Problem: However I'm getting the error could not change directory to ...
0
votes
1answer
51 views
PostgreSQL, How to keep only one schema?
I don't want some users be available to create schemas, and only use the public schema.
I'd like to remove the permission of creating schemas, but I can't found anything on PotsgreSQL 9.1 ...
0
votes
1answer
228 views
How to insert binary data into a PostgreSQL BYTEA column using libpqxx?
I'm a newbie at SQL, and even greener when it comes to using the PostgreSQL C++ API libpqxx.
What I'd like to do is insert some binary data into a BYTEA column, but I find the Doxygen output is ...
1
vote
1answer
74 views
Pass a table array as a parameter to an upsert function in postgresql
i have an UPSERT function which works well but i update and insert records in batches, can this function be modified so that i just pass all the records to this function as an array then it will ...
1
vote
2answers
260 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;
3
votes
3answers
338 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 ...
0
votes
1answer
42 views
Postgres 9.1 wait events
Is there anything in Postgresql 9.1 similar to wait events dynamic views of oracle?
I need to find queries which are waiting for a long time, and the events they are blocked on.
1
vote
0answers
52 views
Write performance of Postgresql 9.1 with read-only slave
I have a Postgresql 9.1 database that is being hosted by Heroku. It currently has a read-only "follower". I need to truncate several large tables (over 100GB) of data and reloads them. Is the ...
0
votes
0answers
130 views
How I can copy from local file to remote DB in PostgreSQL?
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' ...
0
votes
1answer
53 views
Set field values to newly imported rows in PostgreSQL table with existing data
I have a PostgreSQL table with existing data, and needs to import all the rows in a CSV file into that table. I am using pgadmin3's Import tool to do the import.
Question: For the newly imported ...
2
votes
1answer
91 views
Limiting number of results in a Partition using OVER(PARTITION BY)
In the following query, why is it that we have to limit the results returned from each Partition by using the clause WHERE foo.row_num < 3 outside of the subquery foo but not from within the ...
0
votes
1answer
18 views
Cannot create perlplu function
Running PostgreSQL 9.1.8 on Xubuntu 12.04, installed from the repos.
From the shell, I have executed:
createlang plperl db_name;
createlang plperlu db_name;
As the superuser running psql, I have ...
0
votes
1answer
190 views
How to use array variable in query in PostgreSQL
Create table t1 ( xcheck varchar[], name text );
CREATE OR REPLACE FUNCTION fn_acgroup(xch varchar[])
RETURNS record AS
DECLARE xrc as record;
execute 'select name from t1 where xcheck @> ...
-1
votes
1answer
77 views
Passing argument in trigger dynamically
CREATE TRIGGER audit_proc_tr
AFTER INSERT OR UPDATE OR DELETE
ON "log".hi
FOR EACH ROW
EXECUTE PROCEDURE "log".audit_proc(argument);
CREATE OR REPLACE FUNCTION ...
1
vote
1answer
108 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 ...
1
vote
1answer
130 views
Correct use of VOLATILE COST (and ROWS) indications in Postgresql stored procedure
While looking at several examples of pl/python and pl/pgsql, I have seen many - but not all - using volatile cost.
ie:
CREATE OR REPLACE FUNCTION my_function()
RETURNS setof record AS
$BODY$
-- ...
0
votes
1answer
144 views
Cannot `create function` in plpython3u, permission denied
As postgres user, I have create extension plpython3u; in my database
then I have set the plpython3u to trusted: select lanpltrusted from pg_language where lanname like 'plpython3u'; returns true
but ...
0
votes
1answer
81 views
Cannot use python3 as stored procedure language in posgresql database
I want to have python3 in my postgresql database for writing stored procedures.
Being in the psql client, when I enter the command create extension plpython3u I get the error:
couldn't open ...
0
votes
0answers
63 views
Streaming replication WAL files pg_xlog
We are using this awesome tool repmgr developed by 2nd Quadrant for streaming replication.
We set 1000 WALs to be archived. What I noticed though is that on master we have about 600 WALs in the ...
