Database

I have IoT things sending timestamps in GMT to a TimeStream table. This means that the data is corrected to GMT no matter where the data was collected. Are there time zone shift functions that I am missing? The only thing I can find (and works OK) is using `time - 5h`. Are there any functions that correct based on an actual timezone?

Hello We are currently using AWS RDS Custom Oracle and the OS disk have a root partition of 10Gb , although allocating a 42 Gb volume . Just to add a cherry atop, there is a 16Gb swap area allocated in between, which make worse to expand to the remaining disk area I could barely get out of os disk full condition killing old log files in /var/log [root@ip- /]# df -kh Filesystem Size Used Avail Use% Mounted on devtmpfs 7.6G 0 7.6G 0% /dev tmpfs 16G 7.5G 7.8G 49% /dev/shm tmpfs 7.7G 785M 6.9G 11% /run tmpfs 7.7G 0 7.7G 0% /sys/fs/cgroup /dev/nvme0n1p1 9.8G 7.9G 1.8G 83% / /dev/nvme1n1 25G 13G 11G 54% /rdsdbbin /dev/mapper/dbdata01-lvdbdata01 296G 25G 271G 9% /rdsdbdata tmpfs 1.6G 0 1.6G 0% /run/user/61001 tmpfs 1.6G 0 1.6G 0% /run/user/61005 [root@ip- aws]# lsblk NAME MAJ:MIN RM SIZE RO TYPE MOUNTPOINT nvme0n1 259:0 0 42G 0 disk ├─nvme0n1p1 259:1 0 10G 0 part / ├─nvme0n1p128 259:3 0 1M 0 part └─nvme0n1p127 259:2 0 16G 0 part [SWAP] nvme3n1 259:6 0 75G 0 disk └─dbn0-lvdbn0 252:0 0 300G 0 lvm └─dbdata01-lvdbdata01 252:1 0 300G 0 lvm /rdsdbdata nvme2n1 259:5 0 75G 0 disk └─dbn0-lvdbn0 252:0 0 300G 0 lvm └─dbdata01-lvdbdata01 252:1 0 300G 0 lvm /rdsdbdata nvme5n1 259:8 0 75G 0 disk └─dbn0-lvdbn0 252:0 0 300G 0 lvm └─dbdata01-lvdbdata01 252:1 0 300G 0 lvm /rdsdbdata nvme1n1 259:4 0 25G 0 disk /rdsdbbin nvme4n1 259:7 0 75G 0 disk └─dbn0-lvdbn0 252:0 0 300G 0 lvm └─dbdata01-lvdbdata01 252:1 0 300G 0 lvm /rdsdbdata du -sh ./*/ 185M ./bin/ 143M ./boot/ 7.5G ./dev/ 43M ./etc/ 124K ./home/ 1.1G ./lib/ 195M ./lib64/ 16K ./lost+found/ 4.0K ./media/ 4.0K ./mnt/ 3.4G ./opt/ 0 ./proc/ 13G ./rdsdbbin/ 25G ./rdsdbdata/ 13M ./root/ 785M ./run/ 46M ./sbin/ 4.0K ./srv/ 0 ./sys/ 72K ./tmp/ 465M ./usr/ 2.5G ./var/ What im planning to do is to allocate a new swap volume , switch it on, kill the old swap and expand the original volume as the most i could. 1) Could this harm any monitoring task in AWS for RDS custom? 2)Any good soul at AWS could look into the automation scripts for RDS custom and make SWAP on a separate volume? , and allocate the 42Gb volume fully for OS? There can happen some bad stuff any OS updates that surely need more disk space

Hi All, In my stored procedure, I have scursor which loops through the table and each record will go through many IF Conditions and as per result I will move further using fetch next while being inside loop itself.I am not able to do BULK UPDATE cause my requirement demand row level processing.It is performing good for just One id, but i have large number of ids which needs to be processed by this sp and time taken is beyond the expectation.How to speed up this stored procedure in Redshift?

Hi All, Under cloudwatch matrix FreeStorageSpace for few RDS instances shows more GB compare to actual allocated. E.g. : Actual allocated space : 100 GB FreeStorageSpace : 103 GB while for other RDS instances it was referring properly i.e. FreeStorageSpace less then actual allocated space. Please advise . Regards, Nikhil Shah

Hi all, accroding to this official documentation, RDS audit log has the following comma-delimited information https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/AuroraMySQL.Auditing.html ``` timestamp, serverhost, username, host, connectionid, queryid, operation, database, object, retcode ``` i've noticed some logs are writing "READ" in place of operation and "proc" in place of object for example, a log might say "some timestamp, prod1, user1, xx.xxx.xxx.xxx, 23, 12, read, mysql, proc, 0 how would you interpret this particular log? and what does it tell me? i've read the official doc but the explanation is a bit vague appreciate your help thanks,

I need to implement a custom propagation algorithm that starts from a given node and visits its neighbors and update their properties accordingly based on the previous node properties. Example use case is, a quality flag that changed in a given node from good to bad and I need to make all the subsequent nodes become bad. What is the recommend way of implement this efficiently in Neptune? (I'm trying to avoid to have client code to have this graph traversal one hop at a time)

2 Columns from a table in a source sql server database 2017 are not being replicated to the target. Some of the other columns related to the same transaction are replicated but 2 others columns defined as TEXT columns are not. Can someone point me in the direction of what could be wrong with these columns for them not be replicated. Thanks

Hi, I can't figure out which scp is Disallowing access to de rds-db:*... We have the Basic, out of the Box scp's enabled. Nothing else. The IAM policy generator states: ``` denied Denied by AWS Organizations. ``` And this also reflects in the IAM RDS connection: ``` FATAL: PAM authentication failed for user ``` Which according to the documentation indeed states the thing that the Policy generator finds... https://aws.amazon.com/premiumsupport/knowledge-center/rds-postgresql-connect-using-iam/

Hi Team, I have configured dynamic scaling for elastic cache for 80% memory usage, adding shards. Configurations : 1- 3 primary 2- 2 replica each 3. cache.r6g.large When it's triggering up, AWS elastic cache modifying the cluster is taking up. Sometimes more than an hour for 14GB of instance. The impact is when modifying timeout is happening in the application. Is there any way to avoid this ?

I am migrating RDS PSQL 11.13 to Aurora PSQL 11.13 provisioned DB instance as a part of that I have created read replica of Auroa PSQL but I couldn't confirm replica status. Replica lag metric in Aurora doesn't show any data? where do I check to confirm sync is up-to-date ?

Hi I was trying to work on a simple problem where I am taking data from 3 csv files in s3(source) and after combining them, I am appending them to a table in postgre sql where my database is by establishing a connection in my glue catalog with that database. But I am facing an error stating that this table already exist and the data is not getting attached to it. Kindly help me if there is anything wrong with this approach

We encountered an xml issue during the poc of babelfish migration, how to rewrite a user define function based on 64-bit encryption that is very common in sql server. The following code you can find on many web site ``` CREATE FUNCTION [dbo].[FnBase64Encode](@src varchar(max)) 　　RETURNS VARCHAR(max) as begin 　　declare @bin varbinary(max) 　　set @bin=Convert(varbinary(max), @src) 　　return cast(N'' as xml).value('xs:base64Binary(xs:hexBinary(sql:variable("@bin")))', 'varchar(max)') end GO ``` We know that the current version of babelfish(1.2.1) does not support xml methods such as xml.node and xml.value, is there any solution or sample code for this case?

Is it possible to somehow filter the API response received after updating an item in DynamoDB? With filter I mean something similar to the ProjectionExpression capability with a query operation, for example. Even though the ReturnValues option does however provide a number of "filtering" options, it does however not permit selective attribute inclusion. Is there perhaps another way to achieve this?

we have created the MWAA env, ECR, SUBNETs, private SGs, VPC - we see NICs attached to the SG access via UI is given - we can login and open UI via web browser. on /home access we see an Ooops screen browsing other menus (i.e. Security, Docs) work - all the other Dags, Admin, Browse menus result in an Ooops no log other than webserver showing some feedback: on /home : psycopg2.errors.UndefinedTable: relation "dag" does not exist how can one run a db init ? we have tried it using via REST API (https://docs.aws.amazon.com/mwaa/latest/userguide/airflow-cli-command-reference.html) - which works for certain commands but the db init returns with "TypeError: 'int' object is not iterable" any help is welcome what is the need of the security group rules (egres, ingres) ?

I had setup AWS Backup some time ago for RDS and, as far as I could tell, it was correct. I was able to restore from it in my tests (from both vaults - one for production with WORM setup, and one non-production vault for resources that are used for development). The backups were also running correctly - I would see them being made each day for the Snapshots and Continuous also looked correct. Just recently - mid last week - I have not been able to see or access backups. When I view the Backup Vaults as the root user of the account, I no longer see any backups from a certain date forward (Creation Time doesn't go past May 12th). When I look at the specific protected resources, I can see creation times sooner than that, but the backups are all prefixed with `rds:` rather than `awsbackup:`. Given that AWS Backup is managing the backups (or should be) this doesn't make sense to me. Additionally, when I actually go to attempt a restore from the console with ANY of these, I receive an error that says: "Failed to retrieve snapshot." Again, I am using this as a root user and it did work previously. Very concerned about this. I would appreciate any help or guidance.

I'm trying to setup cascading replication from RDS PosgresSQL instances, using logical replication (test_decoding), that will be used by Fivetran. As I don't want to connect any 3-rd party tools to Primary database, I'm trying to setup logical replication on replica, so the flow will look like: Primary (RDS.Postres with HA) -> Replica (RDS.Postres RO replica) --(test_decoding)--> Fivetran. I've followed the steps from this article: https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/CHAP_PostgreSQL.html#PostgreSQL.Concepts.General.FeatureSupport.LogicalReplication (setup parameter & restart database). Seems logical replication was successfully enabled on Primary (wal_level = logical) but the same steps on replica are not working (wal_level = replica). I've tried with different PG versions as there was a mention, that cascading option is available since 14.1 - I'm currently using 14.2 with no luck. Is there anything more I need to enable in order to get this working? Or is such design not supported at all on Manged RDS PostgresSQL Replica?

HI Team, I see only 12.1 & 19c allowed on RDS custom... is it possible to install other versions of Oracle as well?

Hello, I have created a PostgreSQL database, including the PostGIS extension, however, I'm unable to create GiST index on my rast column. I'm trying to execute the following line: ``` CREATE INDEX IF NOT EXISTS rast_index ON public.raster_data USING gist(rast); ``` I get the following response: ``` ERROR: data type raster has no default operator class for access method "gist" HINT: You must specify an operator class for the index or define a default operator class for the data type. SQL state: 42704 ```