Questions tagged [postgresql-9.1]
PostgreSQL version 9.1
286
questions
0
votes
1answer
46 views
Cannot restore data using Postgres WAL files
I'm a newbie at PostgreSQL.
I'm trying to setup a streaming replication (postgresql 9.1).
And I want to test backup & restore on local.
I also made backup files successfully (I think so) by using ...
0
votes
0answers
27 views
Allowing other host to connect to postgresql-9.1 and restart services in postgresql-9.1
We need to add few Ip address in our pg_hba.conf file so that some new servers can connect to our postgresql database. We did the changes to pg_hba.conf like below
old pg_hba.conf
# TYPE DATABASE ...
0
votes
1answer
225 views
How to change pg_largeobject data from psql? (posgresql-9.1)
16-byte password hashes of some application are stored in the postgresql-9.1 database as LOB data. (I don't know why)
So I can get hash:
# select * from pg_largeobject where loid=16916;
loid | ...
9
votes
3answers
2k views
Is the keyword “ALIAS” actually used?
According to PostgreSQL 7.1 through 9.1 (now unsupported), ALIAS is listed as a reserved word, at least for SQL-99. Later versions do not show it - suggesting that it has been dropped as a reserved ...
asked Jan 14 '19 at 15:29
MickeyfAgain_BeforeExitOfSO
6011 gold badge5 silver badges10 bronze badges
2
votes
1answer
37 views
ExclusiveLock on related table due to update?
Recently ran into the following:
Consider the tables created by this:
create table table_a (
id bigserial not null,
last_update timestamp,
primary key (id)
);
create table ...
1
vote
2answers
241 views
Strip longest common suffix and concat in Postgres
Given a table t:
id | name
------------
1 | abcfug
1 | deffug
1 | hijfug
2 | etc
How can I do something like:
select string_agg(strip_lcs(name), ', ') from t where id = 1
returning:
abc, def, ...
0
votes
2answers
33 views
select newest update record among similar record
I have 2 tables segments and summaries.
I want to list the shows in a date interval and just show the latest updated segments (in bold) since it is the freshest copy of the segment for that person.
...
0
votes
2answers
47 views
Number of times PostgreSQL database restarted
How to get how many times my PostgreSQL database restarted and how to get each restart time?
I do not want to get the details from the log file. Is there any other way to get those details?
0
votes
1answer
42 views
To get rid of heavy archive logs in PostgreSQL 9.1
In our production server which is having PostgreSQL 9.1 installed, too heavy archive logs are being generated, approximately 41 GB/day. Heavy updates, deletes and inserts are the cause of this. Can ...
0
votes
0answers
24 views
Gathering all statements executed in PostgreSQL
How to get all the queries ran in PostgreSQL 9.1 database without using log_statement setting
3
votes
1answer
4k views
1
vote
1answer
96 views
Hive metrastore Database in Postgresql External [closed]
We recently migrated hive metastore database from embedded postgresql(coming with cloudera hadoop) to external postgresql(standalone in vm server). After that we notified that there is increase of 5 ...
0
votes
1answer
116 views
Problem joining two tables on date field?
im using following query with PostgreSQL 9.1.13:
SELECT
day::DATE AS date,
COUNT(s.id) AS sale_count,
COUNT(c.id) AS claim_count
FROM
GENERATE_SERIES('2017-08-10', '2017-08-13', ...
1
vote
0answers
196 views
Postgres not able to recover in replication mode
Because of manual cleanup of WAL files, my DB is corrupted. PostGres is throwing this error :
2017-08-10 20:44:41.887 UTC 2661 LOG: database system was interrupted;
last known up at 2017-08-03 ...
1
vote
2answers
1k views
postgres 9.1: role public does not exist
I understand public is not a typical role in postgres:
dav-gis=# \duS public
List of roles
Role name | Attributes | Member of
-----------+------------+-----------
But why can I revoke ...
1
vote
0answers
152 views
Query on postgres 9.5 several times slower than on postgres 9.1
I am running performance tests on two systems that have the same postgres database,
with the same data. One system has postgres 9.1 and the other postgres 9.5.
There are only slight differences in the ...
2
votes
2answers
7k views
Efficient PostgreSQL Updates Using a Temporary Table
I've written a small program to import product-detail updates from file which takes much longer than expected. (I'll use trimmed-down examples for the sake of brevity.)
The program does the following:...
2
votes
1answer
44 views
Postgres Trigger function - Pg 9.1
There is a table on my database that stores a path for video files used by my website. Those files are stored in a SSD disk.
path character varying(255)
id serial
The path will always be like this:
...
1
vote
1answer
435 views
Postgresql Log “^M” Meaning
I am having a bit of trouble trying to figure out why a query is taking long to execute.
When I check log it seems the log may be either incomplete.
My question is, what is ^M? I see an error ...
1
vote
1answer
1k views
REGEXP in a where clause
I store s3 bucket's path for files in my database. Files can be either pictures(jpg) or pdfs.
Let's suppose I have uploaded photo.jpg to my database via application, path will be like:
/{s3bucket}/...
1
vote
1answer
347 views
Postgres slow select query on another table after other table alter and update
I have postgresql 9.1.14 (upgraded a few weeks ago from 9.1.3, but no problems up till now).
I've recently altered several tables by adding a new UUID column and have been updating these UUIDs to ...
1
vote
2answers
1k views
Sort name by Thai character is not working
I am trying to sort a table by name. The name are in Thai character;
>Select name from product_categ
name
--------------------------------------------
...
2
votes
2answers
97 views
Get the date interval from table rows, based on a start stop column
I have a table returning the following result set:
mydate | startstop
------------+----------
2018-02-07 | start
2018-02-14 | stop
2017-02-06 | start
2017-02-12 | stop
2016-02-05 | start
...
3
votes
2answers
324 views
> 30 sec slow query
I've got a very slow query, running over 30 seconds:
SELECT DISTINCT id10
FROM
(SELECT j.id AS id10,
j.modified,
j.n_type AS n_type5
FROM note j
WHERE j.modified_date >=...
0
votes
1answer
196 views
Migrating PostgreSQL 9.1 database to Ubuntu 14.04
I want to migrate a database from PostgreSQL 9.1 installed on an old Debian system to Ubuntu 14.04. The database is making use of triggers and it is a requirement that the triggers be migrated as well,...
3
votes
1answer
281 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
1k 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
392 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 ...
5
votes
3answers
9k 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
1answer
171 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
243 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 ...
1
vote
1answer
2k 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 ...
6
votes
1answer
4k views
postgres backup for specific table with blob oid
I have a table which has an oid column. When I'm taking a backup of this table, it takes complete oids (complete blob / oids backup). But my table only has 10 entries (which is approximately 30 ...
0
votes
2answers
7k 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
530 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 ...
6
votes
2answers
9k 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:...
5
votes
1answer
6k 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
102 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
142 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
142 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
1k 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
3k 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
74 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
1k 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
767 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/...
6
votes
1answer
9k 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 ...
2
votes
1answer
3k 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
951 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
31 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 ...
4
votes
1answer
2k 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 ...
