Questions tagged [postgresql-9.5]

Specifically for PostgreSQL version 9.5

Filter by
Sorted by
Tagged with
0
votes
0answers
17 views

restoring sinnge PostgreSQL DB on RDS- AWS

AWS doesn’t make it quite simple to restore just one DB on a RDS. I came up with below plans for DR and would like to know your feedback. • Our current RDS-T has D1, D2, D3 and D4 postgresql ...
0
votes
1answer
36 views

Unused Indexes - PostgreSQL Database [closed]

I have used attached query to check unused indexes in my EDB9.5 DB. It's showing attached indexes are the unused indexes. Is it safe to drop below unused indexes? SELECT idstat.relname AS ...
2
votes
1answer
56 views

Create cyclic pointers in postgresql 9.5

The problem is to write the "ADD-VALUE" request defined below. For each "cat" value from the table below, keep only 3 records. Figure this table: id cat value updatedAt 1 cat1 v1 ...
0
votes
0answers
32 views

Is it possible than two simple updates on same table interlocks (deadlock)?

Table A is: id integer version varchar data jsonb (large data 1mb) fkToBid integer (references B.id constraint) Table B is: id integer other... Processes are aggressively running the two updates ...
0
votes
1answer
28 views

Postgres DB Timezone Change

Can You please guide us on how we can change the time zone of the Database to UTC which is migrated from AWS which was in time zone UTC? Now the Postgres 12 is installed in Ubuntu 20 which shows (show ...
0
votes
2answers
38 views

PostgreSQL 9.5 database showing signs of corruption in logs, though clients work normally

We have a database server running PostgreSQL 9.5 on a Ubuntu 16.04 LTS box, with the total data size just under 30 GB. We've set archive_mode to on, and archive_command is rsyncing WAL files to ...
0
votes
2answers
18 views

Postgres Specific table access

We have a requirement in our Postgres Database, We want to give specific table permissions to a particular set of user, we also have airflow in our environment which syncs tables, but sometimes any ...
0
votes
1answer
85 views

pg_upgrade from Postgres 9.5.5 to Postgres 12.3 errors out

I am trying to upgrade the database cluster from 9.5.5 to 12.3 using pg_upgrade. I ran it with the --check option, and the result came back "clusters compatible", but when I run it with the -...
0
votes
1answer
42 views

Drop tables but space not claimed in postgres 12

I have upgraded Postgresql 9.5 to Postgresql 12.4 a few days back using pg_upgrade utility with link (-k) option. So basically I am having two data directories i.e. One is old data directory (v9.5) ...
0
votes
3answers
50 views

Postgres 12 drastically slower than postgres 9.5 with identical queries from import/export

I setup a new postgres database (v12) by export/importing from an old one (v9.5). When I query the new one, it's drastically slower despite being on a more powerful machine. Shared buffers are set to ...
0
votes
1answer
122 views

PostgreSQL: pg_upgrade 9.5 to 13 failing with “connection to database failed: FATAL: database ”template1“ does not exist”

I'm trying to upgrade a 9.5 server to 13 but the pg_upgrade process is failing after the main checks. Server is running CentOS 8 on EC2 and is using the pgdg repo installs of each server. bash-4.4$ /...
1
vote
2answers
99 views

Postgres Upgrade Issues with pg_upgrade

I am trying to upgrade 9.5.5 to 12.3 using pg_uprade, got the following error Old and new pg_controldata maximum relation segment sizes are invalid or do not match 9.5.5 Blocks per segment of large ...
0
votes
1answer
66 views

Performance issues when INSERTing in one table, but not another that has many more indexes

I have the following table DDLs (names changed): (note the UUID PK) CREATE TABLE myschema.item ( item_id uuid NOT NULL DEFAULT uuid_generate_v4(), ref_id uuid NOT NULL, type_id uuid NOT ...
0
votes
1answer
48 views

Problem Insert Data to connected tables postgresql

I have 2 existing tables: ‘projects’ and ‘units’.The two tables are connected through the project id. In the table 'projects' the column is called ‘id’ and generates every time a new project is ...
0
votes
0answers
28 views

Poor performance in a SELECT using Inheritance

I will detail my problem in good detail. I have a marketplace application so I make a decision to make a multitenant per schema approach. I like this approach because of the isolation of data, but I'm ...
0
votes
1answer
96 views

could not connect to server: can't connect postgres on the server to postico on my local

when I connect postgres on the server to postico on my local I get this error could not connect to server: Operation timed out Is the server running on host "http://159.65.65.203/" (92.242....
4
votes
2answers
541 views

How can I safely rename a sequence in PostgreSQL, ideally without downtime?

We want to rename some sequences in our production database for certain reasons. Is there a way to do this safely in production without having to first close all connections to the database? It's ...
0
votes
1answer
149 views

Default Postgres privileges not showing on \ddp

As far as I understand there are set of default Postgres public privileges. I have created user and granted him privilege using: CREATE USER foo WITH PASSWORD 'foo'; \connect bar; GRANT SELECT ON ALL ...
0
votes
0answers
16 views

Returning the column names used in Query

I am working with a tool that connects to PostgreSQL, which returns the result of a query in a multi-dimensional array, but, I need the name of the columns used in the query, is there any way to ...
0
votes
1answer
240 views

Postgresql: Permission denied to schema to user created based on master with rights

I'm trying to write a function that eventually will rotate users. Currently I have the code below which works and creates a new user using the prior user which ultimately has the same rights as the ...
0
votes
0answers
17 views

How to monitor incoming streaming replication on hot standby server running version 9.5?

We have a PostgreSQL cluster running version 9.5. I know that version 9.6 would support view pg_stat_wal_receiver that included the info I'm looking for. Is there any way to check if streaming mode ...
0
votes
1answer
354 views

What does pg_stat_all_tables.idx_scan actually mean?

We have a busy PostgreSQL 9.5 server with OLTP-like traffic where pg_stat_all_tables.idx_scan values go up with considerate rate while we're having performance problems. Does that (idx_scan ...
0
votes
1answer
59 views

Gap Finding - Dates

Essentially i face a islands & gaps problem but unlike many of the examples i found i do not have a unique ID to group over to find my gaps. Data is timestamp without timezone formatted and an ...
0
votes
0answers
1k views

Error: duplicate key value violates unique constraint, but the column does not have a unique constraint? postgreSQL

By the way, I'm trying to automate the below using python, but I've isolated the SQL code and tried it by itself on pgAdmin III, and it still doesn't work. So I have a table called tableName, and I ...
1
vote
1answer
189 views

What does it mean when Postgres query state is null

What does it mean when a query state is null? my_database_name> SELECT pid, datname, usename, application_name, query_start, state, state_change FROM pg_stat_activity; +-------+------------------+-...
0
votes
1answer
98 views

Secure postgres 9.5 database packed on Windows desktop application

My desktop app includes a postgres database and a front app. I need the db to be accessed only by my front app. The credentials of this user are going to reside in the front app binaries. I realized ...
1
vote
2answers
2k views

Postgresql query is very slow for my big table

My database version is postgresql 9.5. create table if not exists request_log ( id bigserial not null constraint app_requests_pkey primary key, request_date timestamp not ...
1
vote
1answer
96 views

Improve Performance by Left Join and Group By Count - Postgresql

I am trying to write a query that does COUNT(*) on GROUP BY and a LEFT JOIN. How do I improve the left join? I am using the below index create index session_opportunity_index on public.session ...
2
votes
1answer
84 views

Postgres not using multicolumn index for queries on leftmost columns?

I'm working in Postgres 9.5.15 and have a table called project: id | integer | | not null | nextval('project_id_seq'::regclass) user_id ...
0
votes
0answers
21 views

Insert the value of column in another column as jsonb

I'm trying to insert the value of one column as the value for another column of type jsonb, so this is what I currently have: INSERT INTO "Customers" ("id", "priceList", "metadata") SELECT id, ...
0
votes
1answer
16 views

how to take postgres server backup skipping some of databases?

I have PostgreSQL server version 9.5. Contains 20 Databases. I want take server backup skipping of some 10 databases. Am using pg_dumpall, but it was taking full server backup. thanks,
0
votes
2answers
44 views

SQL cannot add columns to query

I have the following query (PostgreSQL 9.5.19): with userlist as ( select a.ips, array_agg(t.super_user_id) as super_users from tmp_table_1 t left join unnest(t.ips) as a(ips) on ...
0
votes
1answer
40 views

How to find out unused port on Linux server

We are trying to use port 5444 for our new PostgreSQL instance and we are not sure whether 5444 is used or unused. Could you please help us to find the answers to the below questions. We want to ...
0
votes
0answers
29 views

Need Suggesion on PostgreSQL Database Minor upgrade Procedure from 9.5.15 to 9.5.19:

Thanks Alot each and every one. Could you please help us to below points: We are trying to deploy the latest postgres minor version 9.5.19. We have deployed postgres server using yum repository. ...
2
votes
1answer
397 views

Can you import a Postgresql 9.5.10 db into a Postgresql 10.9 server?

I would like to know if it is safe to import a dump made from a database running on a PostgreSQL server 9.5.10 to a PostgreSQL server 10.9. Basically I am able to do the import. It does not fail. But ...
-1
votes
1answer
74 views

append the text to postgres crosstab query [closed]

Am using below crosstab query: SELECT * from crosstab('SELECT id,task_id, status_id FROM table ORDER BY 1,2') AS final_result(id character varying(50), task1 int,task2 int,task3 int,task4 ...
0
votes
0answers
448 views

pgBouncer exceeds connection limit even with idle connections

I'm using pgBouncer to balance connections in PostgreSQL. Sets a limit of 2048 connections in the PostgreSQL main configuration and also in pgBouncer. It happens that there are several connections ...
4
votes
2answers
256 views

GIN index ignored when query has many (more than 18) OR statements

I have an issue where the execution is not using a GIN index when going over 18 OR statements. This is my index: CREATE INDEX emailjobs_externalreferences_gin ON vsko_mailer_api_prod.emailjobs USING ...
0
votes
0answers
68 views

DDL Logs In Postgresql.Conf File

I want to log only ddl commands to my postgresql .wal files. So, i configured my postgresql.conf file to log those sql queries but it did not work. It cathes every query that works on my postgresql ...
0
votes
4answers
2k views

Postgresql extremely slow count (with index, simple query) [duplicate]

I need to run these simple queries on a table with millions of rows: SELECT COUNT(*) FROM "subscriptions" WHERE "subscriptions"."project_id" = 123; SELECT COUNT(*) FROM "subscriptions" WHERE "...
0
votes
0answers
2k views

Unit postgresql.service failed to load: No such file or directory

When i try to connect to my postgres database, i get the error below: could not connect to server: Connection timed out (0x0000274C/10060) Is the server running on host "10.2.1.96" and accepting ...
1
vote
1answer
411 views

Postgres table with 35m rows slow queries. how can i improve the performance?

SELECT count(c.id) as clickCount FROM clicks c LEFT JOIN links l on c.link_id = l.id LEFT JOIN user_agents ua on c.user_agent_id = ua.id AND ua.robot IS NULL WHERE l....
0
votes
1answer
247 views

How to pivot multiple columns in PostgreSQL 9.5?

I have the sample data as follows and want to find if I can use crosstab function in this case. Sample Data: ITEM | CD | TYPE |PARTS | PART | CNT | Item 1 | A | AVG |2 | 1 | 10 | Item ...
1
vote
3answers
604 views

Take average of multiple nullable columns

I have an example table (year) as below in PostgreSQL 9.5: Name | 2010 | 2011 | 2012 ------------------------- A | 10 | | 40 B | 10 | 20 | 30 Now, if I write a simple query as ...
0
votes
0answers
208 views

Select query taking too much time in postgresql

I have the following table in postgresql 9.5 database on ubuntu 16 (4 gb ram, 2vCPU): create table stat_data ( name varchar NOT NULL, the_date date NOT NULL, uopen numeric, uhigh ...
0
votes
1answer
28 views

argument 'and' incorrect, sum values of attributes

excuse the disturbance, but a comparison does not give me a boolean value, because the sum of the attributes does not return an integer value, but bigint. How do you add up the values of three queries?...
0
votes
0answers
45 views

how to insert an if check in an insert

I have an after insert at the statment level, which must insert the positive or negative result in an attribute based on the count of the values of other attributes. But control structures cannot be ...
0
votes
1answer
271 views

how to get column count in postgreSQL

i have a query below select emp_id, sum(case when leave_type = 'Planned' then 1 else 0 end) Planned, sum(case when leave_type = 'Not Informed' then 1 else 0 end) NotInformed, sum(case when ...
0
votes
0answers
50 views

Postgres break table columns into multiple tables having unique rows

I have this table called transactions. It has two columns id and report_id. id is primary key. I want to create two new tables transaction_ids and report_ids both with just one column id. Both these ...
0
votes
1answer
44 views

How to calculate quantity of WAL in db without archiving?

I would like to find out a quantity of WAL files which are being used in PostgreSQL database in last 24h. I was assigned to do Log-shipping but before I do it I want to know how many WAL files are ...

1
2 3 4 5
7