Questions tagged [postgresql-9.3]
PostgreSQL version 9.3
580
questions
0
votes
1answer
22 views
PostgreSQL: relation would be inherited from more than once
Assume I have a schema which initially have several yearly sliced data like:
schema: land_price
- land_price_1990
- land_price_1991
...
- land_price_2010
Now I would like to create a parent ...
0
votes
1answer
34 views
How is a table locked regarding UPDATE-SELECT queries in PostgreSQL?
Is it possible to combine these UPDATE queries?
UPDATE myTable1 SET fld1 = myTable2.value
FROM myTable2
WHERE myTable1.id = myTable2.id AND myTable2.style = 45;
UPDATE myTable1 SET fld2 = myTable2....
0
votes
1answer
9 views
Postgres record locking issue - could asynchronous UPDATES be a solution?
Is it possible (and sensible) to issue asynchronous UPDATEs to a postgres table?
Here’s the issue. I have two process, both of which access the same table.
Process “A” loads vast quantities of ...
0
votes
2answers
26 views
Speeding up Postgresql manual index reset?
I'm merging databases with each other. One problem I have is that both of the database tables have the same primary keys (starting from 1, but otherwise they are different records).
To solve this, I ...
0
votes
1answer
35 views
found xmin X from before relfrozenxid Y during vacuum
The vacuum of two pg_catalog tables runs into the following error and stops there.
found xmin X from before relfrozenxid Y during vacuum
I could find a lot of discussions about this problem but ...
2
votes
1answer
25 views
pg_commit_ts growing in postgres instance
I have a customer who is trying to set up bidirectional replication with logical replication publication/subcription model across two instances with the help of last_update_wins and reported that with ...
1
vote
1answer
38 views
Dynamic pivoting in PostgreSQL
I have a scenario where I have to pivot a list of rows to columns dynamically .For a particular order_number there might be several products. I want all those products to be in a separate column, ...
0
votes
1answer
51 views
Can I do vacuumlo,vacuumdb and reindex in live production system without any downtime in postgres database
We are using a Postgres database (9.3) as our production database. It is generating huge lo objects. Due to this, the size is growing very fast. So we need to do database cleaning frequently. To ...
2
votes
0answers
38 views
the cost decreased but the time was practically the same, why?
Table definition:
CREATE TABLE schema.mylogoperation (
id_mylogoperation serial,
data DATE,
myschema VARCHAR(255),
column_var_2 VARCHAR(255),
user VARCHAR(255),
action TEXT,
...
0
votes
0answers
39 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
65 views
Huge DB (> 600M records/table) times out on query or hits memory limits?
Dearest DBA friends of Stackexchange,
I am trying to write data from multiple sourcetables into a new table containing compound information that i wish to use as source for a later project. The ...
0
votes
0answers
11 views
Postgresql 11 systemd file changed reload to kill; why?
I'm upgrading from Postgres 9.3 to 11, and I do not understand why the ExecReload default command went from
ExecReload=/usr/pgsql-9.3/bin/pg_ctl reload -D ${PGDATA} -s
to
ExecReload=/bin/kill -...
0
votes
0answers
584 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 ...
1
vote
1answer
204 views
install postgresql in unattended error
I try to install PostgreSQL within myapp.exe (which used advanced installer to build) in unattended mode,
I set parameters as below:
--mode unattended --superpassword xxxxxxx --prefix c:\programs ...
2
votes
2answers
142 views
Performance of large transactions and concurrency?
If I have a multi-million row table and I run a transaction that updates 50k rows, what are the performance implications of this?
Assuming it's indexed correctly, it shouldn't take long, but what ...
0
votes
1answer
59 views
Move a Postgres 9.3 database to a new server and update to 9.6 with minimum downtime
I have a PostgreSQL 9.3 database, which I have to move to a new server and update to 9.6 with minimum downtime.
I thought I could :
on the old Postgres, activate archive_command to copy WAL to a NFS ...
0
votes
1answer
158 views
Problem migrating PostGIS
I need to migrate Postgres 9.3 + PostGIS 2.1 to Postgres 10.
I'm using CentOS 7.X
Using pg_upgrade it should be quite straightforward, but the problem is PostGIS.
Postgres 9.3 has PostGIS 2.1 ...
1
vote
0answers
158 views
There seems to be a postmaster servicing the old cluster. | Upgrade from postgres 9.3
I am working on upgrading postgres from version 9.3 to 10. I have encountered a lot of issues along the way since the install is way more custom done than it should be (People here LOVE complex and ...
0
votes
1answer
628 views
Postgres taking 3h to DELETE CASCADE
I have a Postgresql 9.3 with a "mother" table containing items, and a number of "child" tables containing parts of different kinds (e.g. item "AAA" is composed of 1000 parts "P1", 800 parts "P2", 40 ...
2
votes
1answer
48 views
How to use “WHERE” and also “WHERE NOT EXISTS” in the same query and table?
I am trying to add both WHERE and WHERE NOT EXISTS in same query. Below mentioned query1 is working fine, but it returns all the t1 data which does not belong to company1. So I tried query2, but no ...
7
votes
1answer
59 views
Postgres: query planner not respecting table inheritance constraint when querying for null
I'm having an issue with slow queries that is caused by the planner checking every inherited table instead of only the one with the constraint.
I have a table with 0 rows called "search_result". This ...
0
votes
1answer
361 views
How to query inside jsonb column
I have a column type of jsonb named changes
THis is the structure of my column.
[["change","is_enabled",[false,true]]]
I want to query within the column.
I want to find all the rows that "...
1
vote
1answer
120 views
How to find the nearest lat and lang in postgresql
I am trying find the nearest lang and lat from the specified lat and lang
i tried the below mentioned query, in my query i have mentioned the place muscat's lat and lang ,so i want to display the ...
0
votes
1answer
36 views
Failover in Streaming Replication
I have one doubt on master to slave replication in streaming replication in postgresql. I confgured ONE Master and One Slave with streaming replication in my system If Master fails slave act as a ...
0
votes
1answer
1k views
If else statement on join query
i am working with a Postgresql database
having two tables:
professionals[professional_id,user_id,account_id]
users[user_id email]
accounts[account_id email]
professionals
[professional_id,...
0
votes
2answers
549 views
Tracking database and table size growth
Is there way we can get the table size and DB size growth from last one month day-wise?
We are able to give the current size of DB and table size.
1
vote
1answer
603 views
Not a able to kill the DB session in Postgres RDS
We are trying to killing one of DB session but not able to do it. Tried with following command getting "TRUE" after issuing command. Still we can see the session is active.
select ...
0
votes
1answer
2k views
Count records with same id but different column value
I have the following table:
+------+------+
|userID|Name |
+------+------+
|1 |sport |
+------+------+
|2 |it |
+------+------+
|3 |game |
+------+------+
|1 |sport |
+------+----...
4
votes
1answer
482 views
Custom location for postgis extension .control file [closed]
I am trying to install the postgis extension on my database, which resides on a web hosting service. The problem is that the control files seem to have to be found in /package/host/localhost/postgresq-...
1
vote
1answer
18 views
How to fetch the records which is belongs to only one group in postgresql
I want to fetch the student_id which is only belongs to one collage
expected op
6 and 7 stud_id's so how can i do this?
_id | stud_id | collage_id | university_id | active
-----+---------+-------...
1
vote
1answer
301 views
How to optimize a fetch into a crosstab
I have a schema like:
[ad_id] . [name] . [valueofname]
1 . name . "brian"
1 . age . "23"
2 . job . "IT"
2 . name . "Jack"
The row name contains ...
0
votes
3answers
42 views
Optimize query to filter active IDs for a given day from big table
I have a PostgreSQL table called users with 4 columns:
user_id integer, -- we can have a lot of the same user_id
name text, -- name of the user
value text NOT NULL, -- ...
1
vote
0answers
69 views
PostgreSQL is not working after installing Postgres-XC
I am using PostgreSQL 9.3 on Ubuntu 17. This set up was working fine, but it stopped working after installing Postgres-XC.
I tried uninstalling Postgres-XC and reverting back the data directory to ...
0
votes
1answer
38 views
Correct way to re-use or recover databases from “PostgreSQL 9.3” to “PostgreSQL 9.5”
I was using PostgreSQL 9.3, then I installed PostgreSQL 9.5, then I unistalled PostgreSQL 9.3.
Now I want to move the database from the folder of PostgreSQL/9.3/data/base of PostgresSQL 9.3 to ...
3
votes
1answer
3k views
column must appear in the GROUP BY clause or be used in an aggregate function
I have a simple table with columns col1, col2, col3. All not nullable.
I want to delete all rows where the tuple (col1, col2) has several entries. Background: a unique constraint for (col1, col2) ...
3
votes
1answer
3k views
How to disable Postgres from listening on IPv6?
I am trying to disable Postgres from listening on IPv6 because of a bunch of strange trace files that are constantly being written to syslog.
I'd prefer not to disable IPv6 in the OS itself, so ...
0
votes
1answer
149 views
How to query row containing the given element inside a json column Laravel Postgres
Hello all I have a postgresql table that has a json column called agents. See below
SELECT agents FROM cnms_rosters;
result
agents
------------------
[1,13,3,16,15]
[12,13,14,15,15]
[...
-1
votes
1answer
3k views
How to update JSONB array in postgres
I have a table with following definition:
create table json_test (
my_data jsonb);
Inserted to it values like this:
{"details": [
{
"f1": "",
"f2": "free text"
},
{
...
0
votes
1answer
305 views
PostgreSQL: How find functions that use sys_eval
my server was hacked, I found this in the postgres log
2017-12-19 06:30:16 COT STATEMENT: select sys_eval('pgrep baou87r1D9;pgrep 9rMhx24;pgrep Ps1fnYF3;pgrep P15xAw4;pgrep 8yV192n7;pgrep ktU5R693w2;...
0
votes
1answer
8k views
Postgres Error: must appear in the GROUP BY clause or be used in an aggregate function
I'm a novice when it comes to Postgres, and I realize similar questions have been asked before, however, I'm struggling to make this query work:
SELECT p_search.id, count(kia.image_id) AS count
...
0
votes
1answer
188 views
Design: Postgres, Parent to Child / Child to Parent / Child to Child | JSONB
I am very new to database design. I am hoping for suggestion or solutions for a feature I am working.
Note: We are majorly using JSONB type in Postgres
Feature:
I have a orders table which would ...
0
votes
1answer
102 views
Cleaning up archives in Postgres after using systemd
I'm using Postgres 9.3 and I have a master database and a hot standby one that is following the master.
The operating system is CentOS 7.
I've changed the way of starting Posgres from SysV to systemd....
0
votes
1answer
99 views
Can PosgreSQL databases be restored from physical files?
We had an EC2 instance running PostgreSQL. We created a new EBS volume for our more important DBs and stored them on the new volume by creating new tables spaces and setting their location. The system ...
2
votes
2answers
486 views
WAL archiving w/ streaming replication
I'm running postgresql 9.3. I've currently got WAL archiving configured, but I'm doubting it's utility based on my configuration. On the master, I've got archive_command set to
rsync -az --ignore-...
5
votes
2answers
281 views
Can simultaneous updates and deletes on a set of tables lead to AccessExclusiveLock being taken?
I am using Postgres 9.3.
I have 2 tables : authn_session and customer. Every authn_session belongs to a customer (and hence has a customer_id as a column which is a FK to customer's id).
Note : ...
2
votes
2answers
71 views
How to efficiently sort a column from two tables?
In our app, we have assignments. We create this by unioning two other tables with a due_date column: checklists and tasks. We want to order assignments by the due_dates. Right now, we are doing ...
2
votes
0answers
59 views
Postgres production server: more rollbacks than commits?
I'm studying a little Postgres 9.3 stats, and I've observed that in my servers, this simple query:
select sum(xact_commit) as commits,sum(xact_rollback) as rollbacks from pg_stat_database;
I have ...
4
votes
2answers
850 views
PostgreSQL extremely slow after upgrade from 9.3 to 9.4
We recently upgraded an Amazon RDS instance from PostgreSQL 9.3 to 9.4. There were no problems during the upgrade, and afterwards we ran VACUUM ANALYZE. Performance is now very slow across the board. ...
0
votes
1answer
378 views
How can I reset the counters from pg_stat_database view?
I know I can reset some counters by running:
select pg_stat_reset()
but some counters like xact_commit aren't reset.
How can I do it?
I've tried also:
pg_stat_reset_shared('database')
but doesn'...
1
vote
1answer
1k views
High delay lag between master/slave in Postgres 9.3
I have a Postgres 9.3 database server with this configuration:
# grep archive_ postgresql.conf
archive_mode = on # allows archiving to be done
archive_command = 'rsync -a %p postgres@...
