Stack Exchange Network

Stack Exchange network consists of 174 Q&A communities including Stack Overflow, the largest, most trusted online community for developers to learn, share their knowledge, and build their careers.

Visit Stack Exchange

PostgreSQL version 9.1

0
votes
2answers
17 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
37 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
0answers
173 views

Backup size is larger than the database size

I am taking a PostgreSQL database backup by using pg_dump method. My database size is 25 GB, but after taking the backup, the backup size is showing as 52 GB. I am totally confused why this is ...
0
votes
1answer
32 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
20 views

Gathering all statements executed in PostgreSQL

How to get all the queries ran in PostgreSQL 9.1 database without using log_statement setting
0
votes
0answers
223 views

PostgreSQL query wise memory utilization

How to gather shared_buffer,work_mem utilization of each queries running on the production server without using explain command in PostgreSQL 9.1
0
votes
0answers
46 views

Explain (analyze,buffer) output in PostgreSQL 9.1

How to get Explain (analyze,buffer) result without manually running it and without the use of 'auto_explain' in PostgreSQL 9.1 of all the queries running in the server
0
votes
0answers
38 views

Utilization of Shared Buffer in PostgreSQL 9.1

One of the Postgres process is using memory which is more than shared buffers. Can anyone explain why it is happening and how to solve this. In our server, shared_buffer = 24 MB, but Postgres process ...
0
votes
0answers
80 views

CLOG Buffer allocation and utilization in PostgreSQL 9.1

Can someone explain me how much memory is allocated to CLOG Buffers and how much is the utilization of this buffer in PostgreSQL ?
1
vote
1answer
396 views

Fragmentation in PostgreSQL 9.1

How can I check for fragmentation in PostgreSQL 9.1?
1
vote
1answer
49 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
47 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
66 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
396 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
84 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
1answer
1k 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
32 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
385 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
221 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
260 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
342 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 -------------------------------------------- ...
3
votes
2answers
68 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 ...
4
votes
2answers
234 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
0answers
53 views

Postgres join table with groupby result is done by sequence scan on the table and not index scan

I have large events table i select subset of data (200k lines) and group it by point - > the result is 600 lines. i want to join the result with location table (2g lines ). Postgres do sequence scan ...
0
votes
1answer
128 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
121 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
277 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
156 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 ...
2
votes
2answers
3k 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
103 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
170 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
1k 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
2k 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 ...
3
votes
1answer
1k 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
808 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
321 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 ...
3
votes
2answers
2k 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:...
4
votes
1answer
2k 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
70 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
119 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
116 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
852 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
983 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
59 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
357 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
560 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/...
4
votes
1answer
3k 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 ...
1
vote
1answer
2k 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
419 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
30 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 ...