All Questions

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

How to start, stop, pause and resume a logical replication setup in PostgreSQL using PgLogical?

I have a publisher node on on-prem 9.4 and subscriber node on RDS version 10.6 and few tables are configured for replication. The current status is replicating. Now for my learning, I want to stop the ...
0
votes
0answers
10 views

Connecting to postgres via ODBC from AWS Oracle RDS

I would like to connect a 12.1 Oracle RDS AWS instance to a Postgres database (also running at AWS but hosted on an EC2 instance). There are a number of examples of how you could do this (eg https://...
1
vote
1answer
35 views

Create a read-write user and a read-only user

I'm trying to do what I think is a common setup for a PostgreSQL database on a RDS instance. I want to have: Either on public schema, or on a custom schema I don't especially care A read-write user A ...
0
votes
1answer
16 views

Detecting Logical Replication Error on Postgres v10 on AWS RDS CloudWatch metrics

https://www.postgresql.org/docs/10/logical-replication-restrictions.html States: "When the schema is changed on the publisher and replicated data starts arriving at the subscriber but does not fit ...
2
votes
1answer
36 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 ...
3
votes
1answer
68 views

Will query continue to run even after network times-out

I have a few long-running queries that I'm executing against a remote instance of Postgres (AWS RDS). When I execute any of these queries, I get a network timeout on my client after a couple of ...
0
votes
0answers
23 views

Is it a good idea to have a read connection point to both write database and read replica?

In my laravel app I have a write connection to a write database, and a read connection to a dns server that is connected to a single read replica (as instructed here) In the past the dns server was ...
0
votes
1answer
27 views

How to troubleshoot hung queries in PostgreSQL RDS?

I have strange behavior from an Amazon RDS instance running PostgreSQL 9.6.11. This is new behavior from a system that has otherwise been behaving properly without any changes to code in the last few ...
0
votes
0answers
31 views

Postgres uses sequential scan with OR condition, but index scan with AND condition

We are doing a full text search on an object. Unfortunately, the object is normalized across different tables, and the full text search results in a condition with several OR, because the term can ...
0
votes
0answers
35 views

Grant Privileges on Production Database Throws “Error: tuple concurrently updated”

I would like to grant read access to all tables in my schema GRANT SELECT ON ALL TABLES IN SCHEMA public TO XXX; While it works on our alpha environment, it fails with our production database: [...
0
votes
0answers
8 views

How to find the master instance of a read replica in AWS RDS PostgreSQL?

I have an RDS Postgres instance whose role is Replica. I do not see the master in the same region and probably is in another region of the AWS account. How do I find the master for this replica? ...
0
votes
2answers
56 views

Running a task without waiting for the results in Postgres 11 on RDS

I've got a question about spawning stand-alone threads on Postgres. Here's what I'd ideally like: Have a postgres-side solution to automatically launch tasks on a schedule in their own threads. ...
1
vote
0answers
26 views

Using AWS RDS Postgres: Create a superuser with access to all tables (also future created ones)

In AWS RDS (A managed Database service), we cannot create real superusers. There is a user RDS calls Master User, which can create new users, databases, tables, etc. However, when another user has ...
2
votes
1answer
103 views

RDS: permission denied for relation spatial_ref_sys when copying

I am copying tables using ogr2ogr from Oracle to PostGIS. When I run try to load these I get, ERROR 1: ERROR: permission denied for relation spatial_ref_sys when copying <table>@AWS
0
votes
1answer
67 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 [...
1
vote
1answer
213 views

AWS RDS (PostgreSQL) - Master user lost all privileges after upgrading from v9.6 to v10.6-R1

I just upgraded my AWS RDS DB instance from Postgresql v9.6 to v10.6-R1. I did this manually using the "Modify" tab in the Databases section under the Dashboard. Immediately afterwards, as the master ...
0
votes
1answer
49 views

Find queries causing a lot of read IO

I'm trying to find the cause of a higher amount of VolumeReadIOPs in our Aurora Postgres instance on AWS. This is what the past 6 weeks look like: I found the cause of the largest period (02/27 to 03/...
1
vote
0answers
104 views

How can I install pgtap on rds postgresql?

I am using postgresql 10.7 under AWS RDS. I downloaded pgtap-1.0.0.zip from https://pgxn.org/dist/pgtap/ because I saw an article in AWS forums saying you can just run pgtap.sql yourself. However, ...
2
votes
0answers
38 views

AWS RDS PostgreSQL: Significant query runtime difference between two (identical?) instances

TL;DR: Solution The problem was that one instance hat magnetic storage attached and was therefore significantly slower. Original Question I have two identical AWS RDS PostgreSQL 10.6 databases ...
2
votes
0answers
772 views

CPU usage constantly high after upgrading from Postgres 9.5 to 9.6 to 10.6

I upgraded my RDS instance (db.t2.medium) from Postgres 9.5 to 9.6 couple of days back. Since the upgrade, the CPU usage was almost 100% all the time. This brought down my application. Presuming that ...
2
votes
1answer
320 views

How to alter system on AWS RDS?

I need to run ALTER SYSTEM SET track_activity_query_size = 16384; So I can see the contents of some long running queries (I can only see the beginning at the default size), but when I try to do ...
0
votes
1answer
175 views

Schema replication Postgresql AWS

Is there a way to replicate Postgres Schemas in AWS into one location for reporting purposes? I have 3 postgres databases (up to date with current version) running in AWS that I would like to ...
0
votes
0answers
22 views

Sharing read/write resources between connections in an unfair way

I have two applications that connect to a Postgresql database. A read-only application querying tuned MVs A write-only application updating the database and the MVs We only really require that (1) ...
0
votes
1answer
459 views

Cannot revoke permissions or drop user in PgSQL AWS RDS

I want to drop the user asd from the DB. So, I've got two DBs: foo and bar and the user apparently has dependencies in both. The DBs have been created from the root user postgres, but all tables and ...
3
votes
0answers
350 views

AWS Aurora Database Corruption

I'm running Aurora Postgres and noticed that metric Maximum Used Transaction IDs is increasing a lot. Trying to manually vacuum the tables that's was not automatically vacuumed lead me to the ...
0
votes
1answer
302 views

Installing extension as non superuser on Amazon AWS RDS

I would like to allow non super users to install a specific postgres extension. Accordingly to the documentation at https://www.postgresql.org/docs/current/extend-extensions.html that is possible if I ...
0
votes
1answer
857 views

Postgres 10.5: Granting full schema access to a user that isn't the owner

So the underlying problem I'm having is trying to migrate my on premise Postgres 10.5 instance to RDS. RDS does NOT give end users the SuperUser role so I'm hitting tons of permissions issues when ...
0
votes
1answer
47 views

Increase in number of temp files being generated after increasing checkpoint_timeout

I have pgbadger set up to analyze the log files from my postgres db. I recently changed a couple of parameters in my postgres config. I changed the checkpoint_timeout from the default value of 300 ...
6
votes
1answer
723 views

What is the apgcc schema in Amazon RDS Postgresql?

Recently a mysterious new schema called apgcc appeared in every database in our testing Aurora Postgresql cluster (eu-west region). It is owned by the rdsadmin account. What is it, and where did it ...
1
vote
1answer
803 views

Question regarding PSQLException: User was holding shared buffer pin for too long

I recently encountered a strange error that I don't quite understand what could cause it: caused by org.postgresql.util.PSQLException: ERROR: canceling statement due to conflict with recovery ...
2
votes
1answer
50 views

Regularly updating table with huge amount of rows

I have a table containing (in addition to some metadata) a score and a boolean flag: CREATE TABLE scores ( score_id SERIAL NOT NULL CONSTRAINT score_pkey PRIMARY KEY, user_id ...
2
votes
1answer
4k views

Best way to pg_dump PostgreSQL on RDS to S3

I need to dump my PostgreSQL on RDS to a file on S3 periodically (to use it elsewhere than AWS). Is there a better way than running a script that runs pg_dump and uploads results to S3 using e.g. ...
1
vote
0answers
20 views

User / Client IP restrictions in Postgres running on RDS

I'm looking to restrict connection access for specific accounts based on the client IP. All of our services are running on AWS so I want an account that has R/W permissions to only be able to connect ...
2
votes
1answer
555 views

Temporarily disable indexes before updating whole table

For testing purposes I periodically restore production backup into a new database (AWS RDS PostgreSQL instance) with pg_restore. In the new database I run SQL queries that anonymize sensitive data. ...
2
votes
0answers
205 views

AWS RDS PostgreSQL - Managing Permissions

Relatively new to AWS RDS PostgreSQL environment. We are creating a database with multiple schemas. Users are grouped by "schema_role" that have privileges to create objects in assigned schemas. My ...
1
vote
1answer
77 views

Migrating PostgreSQL DB from AWS to Alibaba Cloud

I'm migrating PostgreSQL RDS instance from AWS located in Frankfurt to the RDS instance in Alibaba Cloud also located in Frankfurt. I use Data Transmission tool from Alibaba Cloud. It connects both DB ...
3
votes
0answers
323 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 ...
2
votes
1answer
802 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 ...
2
votes
1answer
376 views

What are the downsides of using PARTITION for multi-tenant access?

We have an RDS Postgres database which keeps individual store data in a common schema with a field called store_id to distinguish between stores. Let's say we have 1MB per store and 10k stores so we'...
1
vote
1answer
69 views

PostgreSQL - automating database grants [closed]

We're managing about 200 instances of postgres on Amazon RDS. The problem that we're constantly experiencing is that our "DBA" accounts can't access stuff. Basic stuff. Tables, views, etc. Is there ...
3
votes
0answers
557 views

Why am I getting permission denied on an ownership change?

My current user is pronto mediapop_staging=> select current_user; current_user -------------- pronto (1 row) This user a standard super user created on AWS RDS. mediapop_staging=> \du ...
2
votes
0answers
108 views

Postgresql- how to rotate table partitions, trunc oldest every day?

I need to create a partitioned table with 4 rotating partitions for 4 days, and every day truncate the oldest partition. I know how to create table partitions on postgres, but I can't understand how ...
3
votes
0answers
406 views

What can cause slow planning times for “select 1”?

I have a database that is used by a Production application. It's an db.m3.medium on Amazon RDS. We've noticed that a lot of queries are starting to get slow that typically shouldn't be slow (e.g. ...
4
votes
0answers
1k views

Queries running very slowly in Amazon RDS PostgreSQL read-replica

We have been maintaining a project which has both web and mobile application platform. The backend of the project is developed in Django 1.10 and deployed in AWS. At the beginning, when there were ...
8
votes
1answer
3k views

Why do I get a PostgreSQL permission error when specifying a tablespace in the “create database” command?

When I create a database in PostgreSQL without explicitly specifying a default tablespace the database is created without issue (I'm logged in as the pgsys user): postgres=> create database rich1; ...
4
votes
1answer
564 views

Why RDS PostgreSQL `MaximumUsedTransactionIDs` not decreasing even after data base seems correctly `VACUUM FREEZE`'d?

My MaximumUsedTransactionIDs seems to keep growing indefinitely, but I have these evidences hinting me it maybe should not be happening that way: $ PGPASSWORD=$MY_USER_LAND_RDS_SUPER_USER_PASSWORD ...
1
vote
0answers
72 views

Improve qps for postgres write

I am trying to improve the performance of my postgres instance in aws. I am using aws rds db.t2.large Here is my table structure CREATE TABLE public.my_test ( test_index1 text COLLATE ...
2
votes
1answer
2k views

How to set the default timezone in PostgreSQL on Amazon RDS?

The default timezone is currently set to UTC. How to replace that default value? I am using PostgreSQL running on top of Amazon RDS
0
votes
0answers
384 views

Once daily AWS Postgres CPU spike caused by mysterious “OS processes”

We've been experiencing huge CPU usage spikes on our Postgres 9.6 AWS RDS so the point we've had to upgrade to a cost prohibitive instance just to handle these once daily spikes. This spike always ...
3
votes
1answer
590 views

SELECT COUNT(*) returning incorrect results

I am seeing some very strange behavior on PostgreSQL 9.6.3 on RDS Aurora. I am getting duplicate results from certain queries: => select count(id) from foos where id = 'deadbeef'; count ------- ...