Questions tagged [postgresql-9.1]

PostgreSQL version 9.1

Filter by
Sorted by
Tagged with
0 votes
1 answer
341 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 ...
user avatar
0 votes
0 answers
62 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 ...
user avatar
  • 53
0 votes
1 answer
387 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 | ...
user avatar
9 votes
3 answers
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 ...
user avatar
2 votes
1 answer
49 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 ...
user avatar
  • 211
1 vote
2 answers
361 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, ...
user avatar
  • 129
0 votes
2 answers
35 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. ...
user avatar
  • 325
0 votes
2 answers
119 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?
user avatar
0 votes
1 answer
102 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 ...
user avatar
0 votes
0 answers
25 views

Gathering all statements executed in PostgreSQL

How to get all the queries ran in PostgreSQL 9.1 database without using log_statement setting
user avatar
3 votes
1 answer
6k views

Fragmentation in PostgreSQL 9.1

How can I check for fragmentation in PostgreSQL 9.1?
user avatar
1 vote
1 answer
109 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 ...
user avatar
  • 453
0 votes
1 answer
255 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', ...
user avatar
1 vote
0 answers
222 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 ...
user avatar
  • 11
1 vote
2 answers
2k 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 ...
user avatar
1 vote
0 answers
154 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 ...
user avatar
3 votes
2 answers
10k 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:...
user avatar
  • 133
2 votes
1 answer
48 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: ...
user avatar
1 vote
1 answer
450 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 ...
user avatar
1 vote
1 answer
2k 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}/...
user avatar
1 vote
1 answer
409 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 ...
user avatar
1 vote
2 answers
2k 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 -------------------------------------------- ...
user avatar
2 votes
2 answers
112 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 ...
user avatar
4 votes
2 answers
467 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 >=...
user avatar
0 votes
1 answer
218 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,...
user avatar
  • 201
3 votes
1 answer
426 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 ...
user avatar
4 votes
1 answer
2k 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 ...
user avatar
1 vote
0 answers
475 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 ...
user avatar
  • 715
5 votes
3 answers
12k 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 ...
user avatar
  • 715
0 votes
1 answer
179 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 ...
user avatar
  • 101
1 vote
0 answers
272 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 ...
user avatar
  • 715
2 votes
1 answer
3k 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 ...
user avatar
  • 715
7 votes
1 answer
6k 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 ...
user avatar
  • 715
0 votes
2 answers
13k 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 ...
user avatar
1 vote
0 answers
697 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 ...
user avatar
  • 715
7 votes
2 answers
13k 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:...
user avatar
6 votes
1 answer
11k 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 | ...
user avatar
0 votes
0 answers
161 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 ...
user avatar
  • 715
1 vote
0 answers
151 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 ...
user avatar
1 vote
1 answer
149 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 ...
user avatar
2 votes
1 answer
2k 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 ...
user avatar
  • 131
3 votes
1 answer
4k 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 ...
user avatar
  • 133
2 votes
2 answers
89 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 -...
user avatar
  • 165
2 votes
2 answers
2k 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 ...
user avatar
  • 715
0 votes
0 answers
814 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/...
user avatar
6 votes
1 answer
10k 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 ...
user avatar
2 votes
1 answer
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 ...
user avatar
  • 715
1 vote
1 answer
1k 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 (...
user avatar
  • 715
0 votes
1 answer
47 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 ...
user avatar
  • 715
5 votes
1 answer
3k 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 ...
user avatar

1
2 3 4 5 6