Skip to main content

Posts

How to Backup postgres globals without sysadmin permission in RDS

For those of my Friends who are on postgres RDS and require to backup the globas for a rds instance you would soon find it can't perform a backup with pg_dumpall --globals-only.  The default user that gets created along with the RDS postgres instance does not have adequate permission to get through the backup processes. Bellow is the message that you would receive.


pg_dumpall.exe : pg_dumpall: query failed: ERROR:  permission denied for relation pg_authid At line:1 char:1

+ & 'C:\setup_msi\postgres\bin\pg_dumpall.exe'  -h XXXXXX.cXXXX... + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~    + CategoryInfo          : NotSpecified: (pg_dumpall: que...ation pg_authid:String) [], RemoteException    + FullyQualifiedErrorId : NativeCommandError

pg_dumpall: query was: SELECT oid, rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolconnlimit, rolpassword, rolvaliduntil, rolreplication, rolbypassrls, pg_catalog.shobj_description(oid, '…
Recent posts

How To Install a msi/exe with powershell

Today I had to programitacally install a .msi and .exe at the start up of an EC2 instance. By default all them code that needs to be executed during a start up is added to the user data of the EC2 instance.

Bellow was the code used to install the msi and exe
start-process c:\setup\<>.msi /gn -wait

However executing the exe required some effort as it needed special switchers
start-process c:\setup\psqlodbc-setup.exe /install /quiet /norestart  -wait-passthorugh

found the extra switcher doing this




Get the Endpoint for a RDS instnace in powershell

If  you are looking for the endpoint for a RDS instnace and wondered where it had gone. You should find it in the Endpoint property in Get-rdsdbinstance. I found the Get-rdsdbinstance a bit misleading , cause i expected the a expandable property to be {} and found a just text.


Get-RDSDBInstance-Region $region|Where-Object {$_.Engine-eq'Mysql'

Setup Psql Manually

When you use RDS as a service , what you soon find out is that you are not aware of the OS, hardware. You are forced to connect to the RDS instance remotely.

I had the urge to copy data into a postgres RDS instnace and the EC2 instnace that we have backed didn't have psql intalled( the command line tool for postgres). The lasting solution was to get a new AMI backe with the shell for psql, Instead i followed these steps to get the psql shell setup.


Copy the bin, scripts folders from a machine where psql is already works. It generally should be found at C:\Program Files\PostgreSQL\9.6 Download the msvcr120.dll ie link (https://www.dll-files.com/download/9c861c079dd81762b6c54e37597b7712/msvcr120.dll.html?c=bWlMa1QxZDBNWjJvU3ZjV25aRUFzQT09) and copy it to the bin folder Open the runpsql.bat and change the new location of the “psql.exe"
Make a shortcut of the runpsql.bat to desktop.


RDS for SQL Server - SQL native backups

You soon realize the AWS platform provides some of the maintenance work that we used to do as DBA's. But you would find there are situation where you require some level of control. In late 2016 AWS provided the functionality for SQL server to perform native backups of its RDS instances extending its snapshot functionality.
Solution will attempt to extend what Jeff bar has blogged by providing few extra screen shots and some additional steps.
Steps to follow
1.) Enable the SQL native backups by adding the SQL_Server_Backup_Restore option group



2.) Associate the option group created in step 1 to the RDS instance





 Once you have created the option group, it is mandatory that you associate the newly created option group to the RDS instance that you intend to run the SQL native backup. Faili

Simple Storage Service-S3

AWS is revolutionizing  many aspects of the lively hood of many folks in the tech industry. Industries have aligned its processes around the many AWS services and depend on the availability and consistency of its services. As a data person , I am supper delighted and totally on board the AWS journey to a brave new world in the cloud.

For those of you who are getting on-board AWS, Simple Storage System(S3) will be one place to start the journey. I have outlined few points on S3 that can help you on your way.



Is Read after write consistency for PUTS for new objects Is known as object based storage Default S3 buckets per account  =100 Storage tiers
S3 Durable , Immediately available , frequently accessed S3-IA Durable , Immediately available , infrequently accessed S3 - Redundancy Reduced  redundancy storage ( data that is easily reproducible , such as thumb nails ,watermarks etc. )
99.99 vailability Glaciar Cheaper than 33 but more costly and time consuming to retrieve data. This should be used …

The impact of CDC To a Async node

Recent as late last week, I ran into a baffling situation where a async node in the MsSQL HA  solution was not in sync for few hours. Eventough, the high watermark kept increasing for the synchronization between the primary and async node's the latency kept increasing significantly. By the way , HA solution is in AWS in and the nodes are multiple regions.

The impact of having a node not in sync
- Transaction log on the primary will blow out to be uncontrollable
- CDC (Change data capture ) will not work

The impact to the transaction log is quite known fact, but what caught me off guard was the impact to CDC. It was later that it occurred that CDC consumes the transaction log agent, and the transaction log agent does not process the log records until they are  harden at all the HA nodes in the availability group. This lead to more concerns on what needs to be done in case of  catastrophic situation ie. When the primary node in a HA configuration is compromised and the sync secondary ta…