PostgreSQL version 9.1
3
votes
1answer
57 views
PostgreSQL 9.1 runs into inconsistent data, but pg_dump corrects the problem?
We are running a PostgreSQL 9.1.20 with a database of about 50GB.
All things works fine.
After some days strange unexpected values appear in numeric columns, like negative or bigger values in ...
1
vote
1answer
54 views
Alter postgresql table with huge data
I have a table in my database with few simple columns and one binary column. The primary key of that table is an integer type.
The problem I am facing is that auto-incremented primary key column had ...
1
vote
0answers
40 views
autovacuum daemon considers pg_largeobject table?
I have a table which is having oid column and few oid column's data might be frequently modified from my application. I thought, it would rewrite the data and replace with new data (basically, thought ...
0
votes
0answers
23 views
Change database permission recursively
I have restored DB(s) from other user's login which is not correct login. So I have tried to change permission one by one.
It could be done by using either generating update script by select or ...
0
votes
2answers
53 views
copy command in postgresql to append data
I am executing copy command from a function
execute 'copy (select * from tableName) to ''/tmp/result.txt'''
It works fine. But, tableName is dynamic and will be having more tables (iterating in a ...
0
votes
0answers
22 views
Optimize and understand this Query Explain - POSTGRESQL
I have this query which is working perfectly fine. But This takes atleast 10 minutes to finish the query. And I have approximately 12m to 19million records in each table.
Need Help to optimize it ...
1
vote
0answers
27 views
will vacuumlo find orphaned objects
A Table (mytable) is having 2 columns
id_ | loid_
(integer) | (oid)
Values are
id_ | loid_
1 | 15001
2 | 15002
Value in loid_ column refers the oid from pg_largeobject. And if I ...
0
votes
1answer
107 views
oid vs bytea in postgres
I have a table called tblA and its having two columns
id_ (numeric 50,0), obj_ oid
For example, tblA having 1 entries
id_ | obj_
1 | 1001
In pg_largeobject table, there are three entries ...
-1
votes
1answer
57 views
how to swap a column order in select statement [closed]
I have a table which is having c1, c2, c3 columns. And I have added one more column called c4. There are some automated scripts running, which will do below query.
select count(1) from mytable order ...
1
vote
1answer
82 views
postgres backup for specific table with blob oid
I have a table which having an oid column. When I'm taking a backup of this table, it takes complete oids (complete blob / oids backup). But my table is having only 10 entries (which is approximately ...
0
votes
2answers
65 views
CASE with max()
I know how to do this in SQL Server but Postgres is different.
I want to return the max employee pay.
Select b.enumber, b.bday,
case when Max(c.id) then c.pay
ELSE c.pay
End As "Current Pay"
From ...
0
votes
0answers
51 views
Is there any way to reduce pg_largeobject size?
The Backup team are concerned about DB backup since it's huge and taking a long time to back up (physical backup - 300 GB - approx 5hrs). 90% of data is in pg_largeobject.
I could remove loid which ...
1
vote
2answers
148 views
Converting rows to columns
I have data like following:
created_at | status
---------------------+-------------
2016-04-05 1:27:15 | info
2016-04-05 3:27:15 | info
2016-04-05 5:27:15 | warn
2016-04-05 10:...
2
votes
1answer
44 views
How to count multiple values of a column as one group? [duplicate]
I have a table main with the following rows:
id | name | rank
----+--------+------
1 | Ali | a
2 | Sami | b
3 | Khan | c
4 | Kamran | d
5 | Imran | e
6 | Asad | a
7 | ...
0
votes
0answers
48 views
How to make sure vacuum is happening in postgres sql db
I have created a table with two columns and created index for the first column. Then inserted 2 million data using generate series function for first column (2nd column is having null). And executed ...
1
vote
0answers
37 views
pgadmin III backup screen freeze
I wanted to get a backup for a very large database with pgAdmin, I started as usual backups, but after a couple of hours it still runs, and I can't cancel it also.
I don't even get to see the file ...
1
vote
1answer
58 views
Trigger for removing group with no users left in
I have three tables: USERS, GROUPS, and GROUP_MEMBERSHIP
CREATE TABLE USERS (
ID BIGSERIAL NOT NULL,
NICKNAME VARCHAR(20) NOT NULL constraint USER_EXISTS UNIQUE,
PRIMARY KEY (ID)
);
CREATE TABLE ...
2
votes
1answer
185 views
Is there some kind of table-level replication in PostgreSQL?
I work with two databases in PostgreSQL 9.1, say A and B, one per application. Database B needs to read (and not write into) one table in database A (say users), but at the same time some tables from ...
3
votes
1answer
113 views
Find current owner of custom data type?
In Postgresql, when I try to run ALTER TYPE x ADD VALUE y; on a custom datatype I get an error stating PG::Error: ERROR: must be owner of type x.
I know this can be solved by running ALTER TYPE x ...
2
votes
2answers
55 views
How to unite 2 subqueries to reduce retrieving data from cumbersome tables?
There are 3 tables: user, conversation, message.
Table user:
id,
login,
last_login_time (time of last user's login),
...
Table conversation:
id,
user1_id,
user2_id,
message_last_read_id_user1 -...
1
vote
2answers
45 views
trim is not working for few string
I want to trim dot (.) from a column value. Just say string is "Mr." and I need to trim the dot from the string. I am expecting "Mr"
Lets say,
I have two fields in my table
id | value
1 ...
0
votes
0answers
163 views
autovacuum launcher process, but no autovacuum workers
Debian Squeeze (yeah, I know), postgresql 9.1. Two similar db servers, one master one shard. postgresql.conf identical between the two besides small memory footprint config differences - all vacuum/...
0
votes
0answers
18 views
log_line_prefix to starts with month in postgresql log
I have enabled the following line to analyze postgresql log in pgFouine
log_line_prefix = '%t '
I am getting log output as follows
2015-12-13 04:30:36 IST [21709]: [4671-1] LOG:
I guess, ...
1
vote
1answer
495 views
Postgres 'could not write to hash-join temporary file: No space left on device'
I was getting this error a while ago and I was curious about two things -
1- How do the internals of postgres work that cause this message to occur in the first place? What is the hash-join in ...
0
votes
1answer
85 views
Connecting existing Postrgres database with pgadmin
I have a pg database and recently I have installed pgadmin3 to view the table and data in it. After installing pgadin3, I am confused how to display my existing pg database in pgadmin3. How to link my ...
0
votes
1answer
160 views
Change postgresql data directory location
Currenly my postgresql service running with
/usr/local/psql/bin/postmaster -d /usr/local/pgsql/data
Due to insufficient disk space (data directory having 10GB of data), I planned to do the following ...
1
vote
1answer
102 views
enabling fsync in postgresql 9.1
currently fsync option is disabled in postgresql.conf.
If I am enable that,
From:
#fsync = on
To:
fsync = on
(I am enabling fsync to avoid data corruption if any failure since my data (...
0
votes
0answers
19 views
Will PostgreSQL server work if I install the service in mounted partition
I have created all tablespaces (only tables created in these tablespaces) in mounted partition, But db is available in pg_default tablespace (root partition where postgresql installed).
I am facing ...
2
votes
1answer
491 views
How to verify that a PostgreSQL base + WAL backup has been restored correctly
Coworkers were trying to extract a PostgreSQL database copy from a backup made at a hot standby in version 9.1, but it wasn't reliable - we would run it daily, but usually it would end up with various ...
2
votes
2answers
310 views
How to convert numeric values to text in sql for select query
I have string values like this
'[123, 124]'
I could trim the value using below command
select trim('[123, 124]', '[]'); --returning '123, 124' as text
I want to pass the value above as
select *
...
1
vote
1answer
151 views
Is my postgres index being used?
I have a large PostgeSQL 9.1 database in production use. I want to free up some disk space. There is a large index, and I'm unsure if it's being used.
select * from pg_stat_all_indexes where ...
0
votes
0answers
114 views
How to disable switch user option in PgAdmin
I have created read only user with following command (executed from postgres user)
create user read_user with password 'read_pwd';
revoke all privileges on database db1 from public;
revoke all ...
2
votes
0answers
78 views
Postgresql delete taking lots of CPU and time
I have the following SQL Query for deleting rows from my table. The query takes a long time and puts a high load on my server.
delete from message_log where from_id = ? OR to_id = ?
My table Schema
...
0
votes
1answer
44 views
What is the best way to create tables for email campaigns?
I have table email_sent with fields:
id
user_id
template_name
sent_at
opened clicks
And now I need to save data which was send for each email. I have several campaigns with differnt data. For ...
0
votes
1answer
44 views
How to read all metadata without select permissions to actual data?
I need to create a user account that can read the metadata for all objects from information_schema
I can access the information_schema with any user but only for tables that the user has select ...
1
vote
3answers
259 views
Why does the query plan still sort a table despite having an index sorted on the columns?
I'm using Postgres 9.1
I have two tables I am joining:
wikidb=> \d page
Table "public.page"
Column | Type | Modifiers
---------...
4
votes
1answer
278 views
Why does Postgres ORDER BY seem to halfway ignore leading underscores?
I have an animal table with a name varchar(255), and I've added rows with the following values:
Piranha
__Starts With 2
Rhino
Starts With 1
0_Zebra
_Starts With 1
Antelope
_Starts With 1
When I run ...
3
votes
1answer
2k views
Can I do VACUUM FULL to pg_largeobject table?
I have two tables (table1, table2) in a Postgres 9.1 database. Both having oid type. Each table 1 million records. And pg_largeobject table size is around 40GB. I have removed 0.9 million records from ...
3
votes
1answer
98 views
Trigger function using current row and current table name as variables (final part)
Like detailed in my first question I have a Postgres 9.1 database with multiple tables that have the exact same column names, they only vary in their column values:
tbl_log_a
tbl_log_b
tbl_log_c
...
...
0
votes
1answer
350 views
Trigger function using current row and current table name as variables
Like detailed in my first question I have multiple tables with identical layout in a Postgres 9.1 DB.
They only vary in their column values:
tbl_log_a
tbl_log_b
tbl_log_c
...
26 tables (from a to z)....
3
votes
0answers
641 views
PostgreSQL trigram GIST vs. GIN indexes
I have a PostgreSQL 9.1 database that contains 10M+ rows and some text fields that need similarity and %word%-like searches, so I decided to use trigram indexes.
Initially, I started to use GIN ...
2
votes
1answer
3k views
pg_restore: [archiver] did not find magic string in file header
I'm using PostgreSQL 9.1 and want to restore backup files generated with pg_dump:
sudo pg_dump -h 127.0.0.1 -U postgres --clean --inserts -E UTF8 -f out.sql database_name
This command generates a ...
8
votes
2answers
354 views
Trigram search gets much slower as search string gets longer
In a Postgres 9.1 database, I have a table table1 with ~1.5M rows and a column label (simplified names for the sake of this question).
There is a functional trigram-index on lower(unaccent(label)) (...
1
vote
1answer
208 views
Any gotchas with changing IP address of Postgres Master?
We have to replace our Master Postgres database and due to restrictions in AWS, we cannot use the same IP address for the new server (not 100% true, but cannot do this until the original is terminated ...
7
votes
3answers
283 views
Can PostgreSQL use nulls in its indexes?
I've been reading this book which says that
The database assumes that Indexed_Col IS NOT NULL covers too large a range to be useful, so the database will not drive to an index from this condition. ...
1
vote
1answer
569 views
Postgresql - Point in Time recovery using recovery.conf and trigger. How to minimize the time waiting for .history files
Following http://www.postgresql.org/docs/9.1/static/continuous-archiving.html, I copy the WAL archives to another machine and apply them regularly. Recently due to pg server crash at my main server I ...
2
votes
1answer
56 views
Use of smallserial gives error with PostgreSQL
I know the very basics of databases. I worked in the past with MySQL and recently switched to PostgreSQL (9.1) with PostGIS 2.0.1.
At the moment I'm designing a new database and I use the tool ...
3
votes
1answer
72 views
How to validate column while creating Table?
Need to validate the column while creating the tables in postgres , An example before developer create table We are in need to validate the Audit columns,
Example :
create table tab_1 (
a integer,
b ...
3
votes
0answers
181 views
pg_dump with ssl certificates
I would like to run the pg_dump command from a remote host off my database server that only allows fully verified certificate based connections. How should I go about doing this? The man pages for 9.1 ...
3
votes
1answer
337 views
Error while restoring Postgres DB Dump
Using below command to restore the database
Restoration command:
cat my_db_dump.sql.gz | gunzip | psql -d new_database -U my_username
Note: my_db_dump.sql.gz size is 4.5 GB, (extracted size will be ...
