Questions tagged [postgresql-9.4]
PostgreSQL version 9.4
505
questions
1
vote
1answer
45 views
PostgreSQL - 1 billion rows table cannot get vacuumed despite no errors
I have a statistics table that should be processed in a scheduled task to remove rows and count them into a separate, smaller table. Unfortunately the cron task failed for some period of time and the ...
-1
votes
1answer
24 views
Single query to replace a value in multiple tables with same column
I am using Postgres SQL. I am having multiple tables say table1, table2, table3, table4, ... with a common column say name in each of the tables. I want to replace a the value 'Jhon' with 'Jimmy' in ...
1
vote
2answers
34 views
Unexpected behaviour for GROUP BY query
CREATE TABLE public.temp (
id integer,
a integer,
b integer,
x integer
);
select x, a, b
from temp
group by id, a, b;
ends up with expected error:
ERROR: column "temp.x&...
1
vote
3answers
71 views
Why is the database size larger than sum of table sizes
I wanted to know why database size is a way greater than thesum of table size in a database? How does it change?
2
votes
1answer
41 views
Caveats of OUTER JOIN on nested JSON value
I'm writing a query which is supposed to find the elements from a list which DO NOT exist in the DB. My first attempt at this was to use a nested query where the first query fetches the ids, then I ...
0
votes
0answers
17 views
create dynamic table in store procedure without using any paramaters
CREATE OR REPLACE procedure create_table_dateWise21()
LANGUAGE plpgsql
as $$
BEGIN
EXECUTE format('
CREATE TABLE IF NOT EXISTS test21 (
nodeip text,
interface text,
nodeip_interface ...
0
votes
0answers
38 views
How to take backup of PostgreSQL 9.4 database to remote server using pg_basebackup?
We want to take the logical and base backup on different server machine in the network. Currently both backups are running on same server machine causing space issues. We have the scripts in place ...
0
votes
0answers
29 views
WAL Archival not working in PostgreSQL V9.4.9 DB server
Our DB servers are on VM, during planned server restart for system patch deployment our Master DB server crashed.
We have taken the clone of Primary Slave VM and restore the Master DB using last ...
1
vote
0answers
20 views
Postgres Major version upgrade 9.4 -> 9.6. PITR compatibility across versions?
Just dug into postgres documentation and couldn't get a clear answer on this. With Debian Jessie hitting EOL for Debian LTS in June, we are upgrading our database servers along with our other servers ...
0
votes
0answers
33 views
Adding key value pair to nested jsonb array object in postgresql
I have two temp tables with my_data column as jsonb type. Table temp2 has actual data lets say
my_data = {"a": [
{
"b": "abc",
"c": "def",
"id": 1
},
{
"b": "efg",
"c": "ghi",
"id": 2
},
{...
3
votes
0answers
330 views
How do I fix multixact wraparound after it has already occurred?
I have a database which has a single table for which MultiXact wraparound has already occurred. Neither manual vacuums nor automatic vacuums are able to fix this condition.
Manual vacuums fail almost ...
1
vote
1answer
102 views
Does the Postgres 9.4.9 update fix MultiXactId “does no longer exist” in addition to “has not been created yet” errors?
Postgres 9.4.9 has a fix in it's release notes which reads (emphasis mine):
Prevent possible failure when vacuuming multixact IDs in an installation that has been pg_upgrade'd from pre-9.3 (Andrew ...
1
vote
1answer
123 views
Does postgres single-user mode use postgresql.conf settings?
Does running postgres in single-user mode (--single argument) cause postgres to start and use the settings present in the postgresql.conf file in the data directory defined by the -D option?
I am ...
0
votes
1answer
89 views
Postgres 9.4.5 small table grows into huge size - periodic VACUUM FULL needed
We are running a PostgreSQL database on Windows Server. The database version is 9.4.5 (it's been running stable for a long time now so we aren't updating it).
Some of our tables have millions of rows,...
0
votes
1answer
110 views
Postgres ignoring GIN index for LIKE query?
I am working in Postgres 9.4. I have a table and index like this:
Table "public.title"
Column | Type | ...
0
votes
0answers
1k 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 ...
0
votes
1answer
50 views
slow count queries - what are we doing wrong?
Using Postgresql 9.6.14, running the following query:
SELECT COUNT(*) AS "__count"
FROM "crm_client"
INNER JOIN "crm_role"
ON ("crm_client"."role_ptr_id" = "crm_role"."id")
INNER ...
0
votes
1answer
173 views
How to extract the grants on default roles of PostgreSQL 10: pg_read_all_settings, pg_read_all_stats, pg_monitor and pg_signal_backend?
I have a Postgres instance of version 10 running on AWS RDS. I have a user there with default roles granted to it. I want to create the same user on my sandbox where the instance is of version 9.4. ...
1
vote
1answer
566 views
Postgres - Vacuum doesn't complete on large/busy table
We have a decently active PG database hosted on AWS. We recently started getting notifications like the following:
Transaction ID age reached 750 million. Autovacuum parameter values for [...
0
votes
1answer
97 views
PostgreSQL Correlation Between “max_connections” and “numbackends”
I'm trying to figure out why the field numbackends in pg_stat_database never goes above 16 (and often stays the same even after a restart).
I have max_connections set to 100 and ...
1
vote
1answer
231 views
move postgresql 9.4 cluster to new server
I have a redmine postgresql folder backup (no dump) from postgresql 9.4
and want to move it to a new server with debian 10 (buster) with postgres 11.
On my debian buster server I tried to get this ...
0
votes
1answer
53 views
Comparing nullable column values using “IS NULL” versus “= NULL”
I'm dealing with nullable values in a column of a table, i'm wondering why this:
with cte as (
select 'c1'::text as c1,null::text as c2
union
select 'c2'::text as c1,null::text as c2
)
...
0
votes
3answers
132 views
How to SELECT “closest” rows from another table?
I have two tables, map1 and map2, there are multiple possible combinations between the columns map1.id1 and map2.id2.
I have tried the below query:
SELECT map1.id1, map2.id2, MIN(...
0
votes
1answer
36 views
Allow transparently altering View's underlying table
How to create a view that does not prevent altering its underlying table? There's a certain reason why I can't drop and re-create the view every time I need to change a column type or add a new column....
0
votes
0answers
221 views
Quering Postgresql digest function inside procedure works on pgadmin but not works by calling via Laravel
recently I have created stored procedure in Postgres Sql which deals about with hashing passwords. When used via PgAdmin tool it works like a charm as stated below.
I have also installed extension ...
0
votes
1answer
20 views
How to perform count(), count(distinct) in the same output using POSTGRESQL
I am trying to perform both count(distinct(keyword)) and count(keyword) from same tables. Here are the two queries.
Query-1
SELECT t1.count(distinct(keyword)) from keyword t1, sent t2,tweets t3,...
1
vote
0answers
78 views
Changing Postmaster File Name in PostgreSQL
I have two instances of PostgreSQL with master-slave replication between them and want to use GlusterFS. Everything works fine until the second instance wants to store the data in the GlusterFS. ...
0
votes
1answer
65 views
FUNCTION BEFORE TRIGGER INEXISTENT OPERATOR date > = double precision
In the 'pubblicazione' table I want to insert some fields of the tuple contained in the 'legge_approvata' table, but to do this the promulgation date must be in the same month and year as the date of ...
0
votes
1answer
172 views
How to make use of WITH in PostgreSQL to reduce query cost
I am trying to get twitter data of narendramodi using below command.
SELECT b.t_id,a.profile_image,b.tweet_text,e.media_media_url,b.retweet_count,b.favorite_count as like_count,count(...
0
votes
0answers
2k views
How to run multiple sql statements in Postgres using the tool PGADMIN?
I have 170 Alter Drop Constraint statement, 120 Truncate table, 120 Insert table and 170 Alter Add Constraint.
All of these should be run in one in one script as batch script and I'm using PGADMIN IV ...
2
votes
1answer
2k views
Use NEW in FROM clause in Postgres trigger?
I am trying to write a Postgres trigger to unnest an array field before inserting or updating a new row. E.g.
SELECT
unnest(something)
FROM NEW
However, this seems to lead to an error:
relation '...
2
votes
1answer
792 views
Streaming replication archive folder taking all space- Postgresql- 9.4
We had recently setup postgres streaming replication between master (physical, CentOS release 6.6) and slave(virtual, CentOS 7.6). We did the test and streaming replication is working fine.
We ...
1
vote
1answer
38 views
Regarding GIN Index in Postgresql
I am facing an issue that Even after creating a GIN INDEX execution plan is taking much time.
select email from customer where lower(email) like '%gmail';
CREATE INDEX idx1 ON customer USING gin (...
2
votes
3answers
86 views
Find all items where the 2 latest statuses meet condition
I have a table in a PostgreSQL database like this:
ID|Item-FK |timestamp|status
=============================
1 | 123 | ... | EXPIRED
2 | 123 | ... | PENDING
...
I want to query all ...
1
vote
0answers
899 views
postgres :: FATAL: could not access file “pglogical”: No such file or directory
We installed pglogical from source code, we are running PostgreSQL 9.4
make USE_PGXS=1 clean all
make USE_PGXS=1 install
Installation was successful after updating the parameters, we tried ...
1
vote
1answer
1k views
Streaming replication version between different minor versions
I want to know few details about streaming replication.
I want to setup streaming replication between version 9.4.4 and 9.4.20 ?
I have the master server in centos6.6 with postgres version 9.4.4 ...
1
vote
1answer
291 views
ERROR: relation “codepool_codepoolseq_seq” does not exist SQL state: 42P01
Below is my script but I keep getting an error.
-- Table: public.codepool
-- DROP TABLE public.codepool;
CREATE TABLE public.codepool
(
codepoolseq bigint NOT NULL DEFAULT nextval('...
0
votes
3answers
5k views
Downgrading PostgreSQL 10 to 9.4
Are there any particular precautions I should take when downgrading PG 10 to 9.4?
I have a database on Heroku (PG 9.4) which I'm going to upgrade to PG 10.6. In case something goes wrong, I want to ...
1
vote
2answers
535 views
How to dump multiple databases of the same cluster in single execution?
I have 100 databases in my cluster. I need to backup 30 databases (as single file or multiple file, anything is fine). I could do it for tables using pg_dump with -t.
Is there any way to do ...
0
votes
1answer
80 views
Streaming replication in postgres question
We have recently setup streaming replication in our Postgres server (t01, t02). t01 is master and t02 is the slave. I want to understand the below two issues:
Recently our /var directory of t01 ...
1
vote
1answer
43 views
Merge 2 columns and replace with specific output
The setup is a Debian 8 with Postgres 9.4 (64bit if that matters).
Given following table I want to "search" for a combination of columns first and column sub and replace their output with another ...
1
vote
0answers
16 views
Calculate percentages by using JOINS result in postgresql
I am trying to calculate percentages by joining 3 tables data to get the percentages of positive_count, negative_count, neutral_count of each user's tweets. I have succeeded in getting positive, ...
0
votes
2answers
41 views
Different results for logically same, but textually different queries
Is there any reason why the following queries would return different results?
select count(id)
from mytable
where
status='active'
and ((textfield1 ilike '%needle%') or (textfield2 ilike '%...
1
vote
1answer
54 views
Postgresql installation, avoid creating “postgres” user
I have a constraint, that I can use only one user on a SUSE Linux Enterprise Server 11.
Now, I need to install PostgreSQL 9.4. Is it possible to somehow avoid the automatic creation of "postgres" ...
0
votes
1answer
3k views
PgBouncer “no more connections allowed (max_client_conn)”
I am using PostgreSQL with PgBouncer, and receive the following error in my application:
08P01: no more connections allowed (max_client_conn)
pgbouncer.ini has the following configuration:
...
5
votes
2answers
2k views
Postgres 9.4 wal_level=logical increases disk space
I am trying to use the AWS Database Migration Service to migrate data from an RDS Postgres instance to an Aurora MySQL instance.
Database Migration Service requires the wal_level to be set as logical ...
4
votes
2answers
102 views
drop database command is cancelled ^C
I want to know what happens when a drop database command is canceled using ^C.
Does it partially removes the data or completely?
1
vote
1answer
72 views
How to get aggregate data from a dynamic number of related rows in adjacent table
EDIT: Unknowing of the rule that prohibits cross-posting, I also asked this on Stackoverflow and chose an answer over there. Since there's another (fully working) answer in this thread though, I won't ...
2
votes
1answer
406 views
How use cascade delete for array field in PostgreSQL?
How can i remove the value from array field?
I need to delete the user id from the university table's user_id array field if exists when i delete any users. so can i do this by using delete cascade ...
2
votes
1answer
618 views
PG_DUMP on Replica DB server Error
I have a master - slave configuration with 9.4 version, but there is no WAL streaming replication configured. Customer simply copy xlog files on network share and replica applies it.
I need to run ...
