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

Questions tagged [postgresql-9.5]

Specifically for PostgreSQL version 9.5

0
votes
0answers
4 views

How to repair corrupted PostgreSQL pg_toast error

The hard drive on my PostgreSQL 9.5 server died, but I managed to recover most of the data. I've restored the database to a solid state hard drive, and I can access most of the data. However, when I ...
0
votes
1answer
13 views

How to select blocks with sequential data and aggregate the ids

I have the following table: id(int) startDate(timestamp) endDate(timestamp) plan_id(int) planned(bool) machine(int) ----------------------------------------------------------...
0
votes
1answer
20 views

postgresql has more DB connection in staging servers than production server

We have postgresql 9.5 where we use it for an application confluence. in staging and in production both max connection is same. But we see staging uses more connection than production e.g staging has ...
2
votes
0answers
12 views

Does VACUUM FULL ANALYZE a partition locks the main table in postgresql?

I'm using postgresql 9.5 and partman for partitioning tables. there is this table that has 30 partitions and volume of one of them are too higher than others. so I need to full vacuum this partition ...
0
votes
1answer
18 views

Update column value divisible by 100

Hello experts I need to update next column value with divisible by 100
2
votes
1answer
64 views

How can it be possible that sum operation is so much faster than count?

When I make a simple benchmark test in a table that has 11.065.763 records. I get the results below: select sum(t.amount * t.exchange) from table t; Finalize Aggregate (cost=658027.39..658027.40 ...
0
votes
1answer
44 views

SQL update multiple rows in destination table with same id but different values from other table

creditNote table id invoice_id generated_at(timestamp) status 10101 111 2018-02-28 14:42:39.247 generated 23982 111 2018-03-30 11:11:11.247 ...
2
votes
1answer
46 views

Cannot run psql in postgresql 9.5

I am using Postgresql 9.5 on Ubuntu 16.04 LTS. I receive the below error when I type psql: a psql: could not connect to server: No such file or directory Is the server running locally and accepting ...
1
vote
1answer
49 views

Truncation vs Deletion When `session_replication_role = replica`

When I first run SET session_replication_role = DEFAULT; CREATE TABLE users ( id serial PRIMARY KEY ); CREATE TABLE posts( id serial PRIMARY KEY, user_id INTEGER REFERENCES users(id) ); ...
1
vote
0answers
14 views

After a PC freeze (Windows 7) and disk integrity check, most data from PostgreSQL table was deleted

My development server is on a PC, using 64-bit Windows 7. My PC has been randomly freezing on me lately, requiring a hard reboot (holding down power button until PC turns off) Yesterday, as I was ...
1
vote
1answer
38 views

Update “oldest” row

I need to update the "oldest" row in the database (for now, its limit 1, but I need to also be able to set it to n). I'm essentially doing a constant stream of "update the oldest rows and retrieve ...
0
votes
0answers
15 views

Method\Function encryption in PostgreSQL

I'm primarily MS SQL Server user. Designing a PostgreSQL database, I wanted to encrypt methods\functions. Is there any way to do this? Thanks!
1
vote
0answers
125 views

ERROR: invalid input syntax for type oid: “english”

While creating an index in Postgres I am getting an error: invalid input syntax for type oid: "english" LINE 1: ...s ON entity_attributes2 USING gist ( (to_tsvector('english',... The query ...
1
vote
1answer
49 views

Should I stop my PostgreSQL server before reboot?

Every week I do an apt-get upgrade and dist-upgrade on the server where my PostgreSQL is running. After that, I do a sudo reboot now. Should I stop the PostgreSQL service before this reboot?
0
votes
0answers
21 views

Reusing the same view or query for different underlying tables

Is there a way in Postgres to make some kind of "parametrized view" e.g. the view that uses the same query but on different tables. I would like to simplify the maintenance of the query below. It is ...
0
votes
0answers
19 views

How to optimize (OpenStreetMap) map tiles rendering?

I'm trying to optimize my OpenStreetMap map tiles rendering. This is one of the queries renderd makes to PostgreSQL: SELECT ST_AsBinary("way") AS geom, "type" FROM (SELECT way, ...
0
votes
0answers
36 views

Running multiple postgres database clusters on the same server

We are currently running postgres 9.5 on rhel7 and have a one database cluster per server. I'm looking at adding multiple database clusters per host, and am wondering whether there are any ...
1
vote
0answers
17 views

Query that fetchs average values over timestamp range of time, on a certain value of another pair of fields

This is the situation, over the time our table reached around 800 millions rows. And it is going to get bigger and bigger. It is a service that collects metrics of energy (voltage, power, current, etc)...
1
vote
1answer
78 views

WAL files not being deleted and pg_drop_replication_slot('barman') causes [55000] ERROR: replication slots can only be used if wal_level > = archive

My database has stopped deleting WAL files and pg_xlog grows to eventually take all my disk space. I have to use pg_controldata and pg_archivecleanup to delete them manually. I'm almost certain it'...
0
votes
1answer
44 views

Is it safe to move big table to another tablespace while working with it?

I have one big bloated table (real data size approx 65 Gb, 140 Gb on disk). Vacuum does not work cause of low space on default tablespace disk. So I want to clean the database from trash by moving fat ...
0
votes
1answer
365 views

Cannot login to postgres with newly created users

I have created a number of users in postgres using createuser --interactive --password but whenever I run sudo -i -u newuser I receive the following error: sudo: unknown user: staging sudo: unable to ...
0
votes
0answers
33 views

create database with pgAdmin4 not working

I've installed pgAdmin4 v3.3 on my local system (Linux Mint 18) with a local Postgresl 9.5. I've got everything up and running and I'm logged in with the master user postgres (with password set). But ...
3
votes
0answers
150 views

Setup a PostgreSQL HA with synchronous replication in windows

I am fairly new to postgres ans would like recommendations for setting up HA system in windows Server machines. My current plan is to run 2 server Primary (Data center) with postgres 9.6 and same ...
1
vote
0answers
85 views

AWS RDS postrgres massive disk use, small tables

I can't figure out why our AWS postgres server has consumed all of it's space. We just had to up the storage space allocated to it, but can't find any hint from postgres that it's using that much ...
0
votes
1answer
83 views

How to swap array values in PostgreSQL.?

How to swap array values in PostgreSQL. For example i have a table that has a column whose data type is bigint[]. It has following value in it. value: {111, 222, 333} My desired output is {333, 111, ...
0
votes
1answer
22 views

Postgres - Revoke database acess from user, but allow replication acess

The DB have a replication server, i need to revoke the permission on the user "user_bi" but allow the acess of this same user on the replication server! REVOKE ALL ON ALL TABLES IN SCHEMA public FROM ...
-2
votes
2answers
85 views

pg_ctl still pointing to 9.5.4 after upgrade to postgres 10 [closed]

I have updated postgres from 9.5.4 to the latest 10.4 on Ubuntu 14. But after installing postgres 10 I am seeing support@platform1:/usr/bin$ ls -lah pg_ctl lrwxrwxrwx 1 root root 34 Jun 13 08:59 ...
2
votes
1answer
262 views

PostgreSQL - can I use logical replication from a replica's WAL?

I would like to use logical replication. However, as PostgreSQL works now, it can eat up the whole disk and then block the database. Especially in Amazon Web Services RDS, this is a problem because ...
1
vote
0answers
9 views

Disconnect specific database from pgpool II

I'm using PostgreSQL 9.5 with pgpool II version 3.4.13 on Ubuntu 16.04.3 I want to drop a specific database so I must disconnect the database from pgpool. How can I do this without disconnecting the ...
0
votes
0answers
93 views

Postgresql Replication stopped unexpectedly Slave server's service is not starting. How can I Resume replication?

Postgres Replication was working fine with Postgresql version 9.5. when the template of a DB with 10gb of data was created and then the slave server stopped. Its Postgres service is not starting now. ...
1
vote
1answer
297 views

Parse error in pg_hba.conf [closed]

Hope this is not as stupid a question as I think it is. But I'm in a loop and unable to find a solution. I installed Postgres in centos machine. The installation worked fine (apparently). Now I have ...
1
vote
1answer
65 views

How to improve performance of query for distinct suppliers in big sales table?

I have two tables, one with store data: Table "public.store" Column | Type | Modifiers -----------+------------------------+----------- id | integer ...
0
votes
1answer
108 views

Reset password expiration date to NULL in PostgreSQL

We are cleaning out/standardising DB user/application accounts on the system that has a mix of accounts that were created using different commands at different times by different individuals. We have ...
0
votes
1answer
253 views

PostgreSQL; Update different column on condition using CASE statement

I have a table which has different types of balance and I need to deduct the balance from different columns based on availability. Here is the query (Sample) I tried to use but it didn't work update ...
0
votes
0answers
28 views

Postgresql function slow down drastically after 2nd query

Issue: After starting a psql session, I execute the following query 3 times, the first 2 times, it runs quickly (651ms); the 3rd time (and after), it slows down drastically (96,176ms). I cannot ...
0
votes
0answers
35 views

JSONB: function which sorts only JSON arrays

I've just finished SQL code which checks if particular "query" fulfils "conditions" stored in rows. If yes, id-s of that rows are returned. This is made up example. CREATE TABLE jsonb_test ( id ...
2
votes
2answers
124 views

Delete records if not present in subquery

I want to delete records from a table that are not found in a subquery. But it's not having the desired result. The table has multiple rows per SKU. Each price row has a valid_from and valid_to date ...
0
votes
0answers
17 views

Postgresql - limit size of logged message

I'm trying to overcome a problem with unreadable postgres log. There is a database that contains (unfortunately) files in column with datatype bytea (bad application design, lot of time to rework to ...
0
votes
1answer
25 views

Wal position to wal archive number

I want to manage the copy of archiving to remote location. For that I want to delete all the archives I copied unless they are required by the standby. So, for running pg_archivecleanup $ARCLOC $...
-1
votes
1answer
663 views

Unpivot a table in PostgreSQL [duplicate]

I need to unpivot a table in PostgreSQL. The first dataset is what I have, the second is what I want: Below is my schema with sample dataset: http://www.sqlfiddle.com/#!17/3f45c/1 My understanding ...
1
vote
1answer
25 views

Can sub-selects change in one single query in a read committed transaction?

I am querying the same table multiple times in a single query in a transaction with isolation level set to READ COMMITTED (which is the default in PostgreSQL). Examples are: BEGIN; WITH first AS (...
0
votes
1answer
33 views

Slow query when using ORDER BY in Postgres

This query SELECT * FROM ( SELECT event_id FROM events_201806 WHERE registry_name='$VCZN://externalcenter/ninaExtCenter_ZN' UNION ALL SELECT event_id FROM events_201805 WHERE ...
0
votes
1answer
31 views

How to store number of years, months and days in PostgreSQL?

How to store number of years, months and days in PostgreSQL ? Example: the life time of bulb is => 3 years, 4 months, 23 days Is there any data type in PostgreSQL
2
votes
0answers
310 views

Bulk Updates (inserts, update) and check constraints

Every now and then I need to update all the records (of one attribute) in a very large table. My default strategy is to create a new table, fill all the new values, create indexes, and rename the ...
0
votes
0answers
35 views

Postgres Create table is hanging for 2 days

We are running a database that nearly 11 TB. We are using PostgresQL 9.5. After the hard restart in adding new replica we are facing the issues that we can't create new table most of indexes are ...
1
vote
2answers
354 views

Optimizing query with type casting

I had a query that was taking way too much time despite having all the indexes I could think of. Eventually, I realized that one of the JOIN .. ON inside the query was casting a column's content to ...
-2
votes
2answers
32 views

Plperlu func with column as param [closed]

I have postgres 9.5, and want to use plperlu as lang to get result from curl put My func is: create or replace function langDetect(str text) returns text as $$ use warnings; use strict; my $str = $_[...
0
votes
1answer
88 views

Schema Dump Without User Mapping Passwords

Is it possible to do a pg_dump --schema-only of the database schema but not include any username/password combinations that are in user mappings? For example, I would like items such as the following ...
1
vote
0answers
318 views

PostgreSQL Cluster vs PostgreSQL Master-Slave Replication

I want to setup PostgreSQL and PostGIS to run some experiments (geospatial queries). If I setup a PostgreSQL cluster, this means that the read requests will be distributed across the nodes of the ...
1
vote
2answers
63 views

How to recover data from a corrupted /base/ directory? [closed]

I have deleted all directory by mistake except from the given link /var/lib/postgresql/9.5/main/base/18686 and so on. I have 4 such directories, now I want to get data back from those folder as they ...