Specifically for PostgreSQL version 9.5
0
votes
0answers
4 views
pgxc_ctl folder not created after installation
I am configuring Postgres-xl using the reference:
Postgres-XL Installation Example on Linux
When I tried to edit pgxc_ctl.conf file, I found the pgxc_ctl default folder missing at home directory. As ...
1
vote
0answers
16 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
18 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
21 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
26 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
9 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 ...
2
votes
0answers
20 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
38 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
38 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
11 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
34 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
77 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
8 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
22 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
116 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
62 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
25 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
38 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
12 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
23 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
60 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
15 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
19 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
225 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
27 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
26 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
155 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
29 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
119 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
31 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
55 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
92 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
40 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 ...
1
vote
1answer
41 views
How to synchronize a Windows database for Linux?
I have some database (PostgreSQL 9.5) on a Windows server, and I would like this data to be synchronized to a Linux server, that is connected in an application.
Is it possible to synchronize?
3
votes
1answer
39 views
How to retrieve the value of deleted key in jsonb?
Using the minus operator of jsonb, we can delete the required key, which is super cool, but what if we need to retrieve the value of deleted key?
Thus, if the key was not found, a null is returned or ...
0
votes
1answer
62 views
pgbouncer bind(): Permission denied
I have pgbouncer running locally with config:
[databases]
* = host=myremoteIP port=5432 user=postgres
listen_addr = *
listen_port = 6432
When I connect using pgsql using:
psql -h 127.0.0.1 -p 6432 -...
0
votes
0answers
93 views
pgAgent does not start with message “ERROR: Couldn't register event handle.”
I am able to start the pgAgent in debug mode on a windows (server 2016) machine with the help of
C:\Program Files (x86)\pgAgent\bin>pgagent.exe DEBUG "host=localhost port=5432 user=postgres dbname=...
1
vote
0answers
30 views
Impact of numerous indexes on query optimizer for a delete statement
We have a 10GB table with 30GB indexes on it. The table consists of a few dozens of attributes a primary key on the id (varchar(32) type) and a dozen of indexes on anything but id key (except the ...
2
votes
2answers
114 views
Postgresql large transaction OOM killer
We have a Postgresql 9.5 running on CentOS release 6.5 (Final)
The server has 16GB of ram.
We are running a long transaction which adds and updates 10s of millions of rows in multiple tables. Besides ...
1
vote
1answer
18 views
Postgresql 9.5 JSON problems
I have to update a complex query that takes JSON fields:
SELECT grower, fieldname as "Field/Block/Block Name", downloaddate, area, LandType
from
SELECT context->>'Source' as Product,
...
0
votes
1answer
36 views
Postgres replication views
I see some discrepancies between two views in postgres as to replication and lag time.
By this query, clearly replication is up:
9.5.6.11
postgres=# select client_addr, sent_location, ...
2
votes
0answers
119 views
Primary key violation
We see records in a table that violate primary key and unique key constraints. What could have caused it? How can we track the problem (what needs to be logged/recorded to understand the situation ...
4
votes
1answer
55 views
“Inner” Group By Query sequential scan
I have inner-query in left-join that is using sequential scan instead of index scan, and I have no idea on how to improve it.
I have two tables:
pages - columns: id
shares - each page has many ...
-1
votes
1answer
32 views
HOW to find a specific record from column [closed]
If my column has record like this:
"0,11.2,2,5,1-2,1,SAMPLE CLOT,',11.2 g/dl,14300,145,negative"
and column data type is text.
How can I find a record <7 (less than seven) from that column.
...
0
votes
1answer
27 views
Is it a good idea to create an index just for new data to increase index creation performance?
I have a table with about 350 M rows with 45 GB, it has around 10 columns ,simplified version below
CREATE TABLE PAYMENT (
ID NUMERIC(20) PRIMARY KEY,
CREATION TIMESTAMP,
VALUE DECIMAL(10,...
0
votes
1answer
303 views
Pull Data from one database and insert to another database ( same schema )
I am currently working on moving data from one database to another in PostgreSQL. I am studying python with psycopg2 to connect and run SQL queries in one database. My problem is how can I pull and ...
0
votes
1answer
141 views
Why Is My pg_xlog Directory So Big?
I am running a PostgreSQL 9.5 datbase. There is a master and a slave that is configured as hot_standby using streaming replication (I don't think this is related).
On the master I have the ...
0
votes
0answers
27 views
Query taking too much time for executing in postgresql-9.5
When I am executing a query it takes approx 13 hrs to execute on production server for fetching only 100 to 200 rows.but if i execute same query on testing database it will execute on only 3 minutes....
1
vote
1answer
134 views
Access denied error when archiving WAL during postgresql 9.5 replication setup
I want to setup Postgres replication on Windows. I followed the given youtube url step by step: youtube video.
For archive_command I use this:
archive_command = 'copy "%p" "\\\\ip-address of ...
