fbpx

RDS

How to Start and Stop the PostgreSQL Database

by

AWS

In Amazon RDS for PostgreSQL, you can control the state of your database instance (start or stop) using the AWS Management Console, AWS CLI, or AWS API. The following are the two modes for starting and stopping a PostgreSQL database instance:

  1. “Start” mode: This mode starts the database instance and makes it available for use. In this mode, you can connect to the database and perform read and write operations. To start a database instance using the AWS Management Console, you can select the instance and click the “Start” button. You can also start a database instance using the AWS CLI with the following command:
				
					aws rds start-db-instance --db-instance-identifier <instance_name>

“Stop” mode: This mode stops the database instance and makes it unavailable for use. In this mode, you cannot connect to the database or perform any read or write operations. To stop a database instance using the AWS Management Console, you can select the instance and click the “Stop” button. You can also stop a database instance using the AWS CLI with the following command:

				
					aws rds stop-db-instance --db-instance-identifier <instance_name>

Note: When you stop a database instance, data stored in memory is lost, but data stored on disk is preserved. When you start the instance again, it will be in the same state as when it was stopped.

Linux

In a Linux environment, you can control the state of a PostgreSQL database using the pg_ctl command. The following are the two modes for starting and stopping a PostgreSQL database:

“Start” mode: This mode starts the PostgreSQL database server and makes it available for use. To start the PostgreSQL database server, you can use the following command:

				
					--Run as postgresql linux user
pg_ctl start -D <data_directory>

“Stop” mode: This mode stops the PostgreSQL database server and makes it unavailable for use. To stop the PostgreSQL database server, you can use the following command:

				
					--Run as postgresql linux user
pg_ctl stop -D <data_directory>

where <data_directory> is the directory where the PostgreSQL data files are stored.

Note: The exact location of the PostgreSQL data directory may vary depending on the operating system and installation method. You can typically find it by looking in the PostgreSQL configuration file (e.g., postgresql.conf) or by using the pg_config command.

systemctl command if you have installed PostgreSQL as a service. The following are the two modes for starting and stopping a PostgreSQL database using systemctl:

“Start” mode: This mode starts the PostgreSQL database server and makes it available for use. To start the PostgreSQL database server using systemctl, you can use the following command:

				
					sudo systemctl start postgresql

“Stop” mode: This mode stops the PostgreSQL database server and makes it unavailable for use. To stop the PostgreSQL database server using systemctl, you can use the following command:

				
					sudo systemctl stop postgresql

Note: The exact name of the PostgreSQL service may vary depending on the operating system and installation method. You can check the name of the service by using the systemctl list-units command and searching for a service with a name starting with postgresql.

Azure

You can stop and start an Azure PostgreSQL server using the Azure portal or the Azure CLI.

Here are the steps to stop and start a PostgreSQL server using the Azure portal:

Go to the Azure portal.
Navigate to the Azure PostgreSQL server that you want to stop.
Click on the “Stop” button to stop the server.
To start the server, click on the “Start” button.

And here are the steps to stop and start a PostgreSQL server using the Azure CLI:

Open a terminal or command prompt.
Run the following command to log in to your Azure account:

				
					az login
az postgres server stop --resource-group <resource-group-name> --name <server-name>

Start the PostgreSQL server by running the following command:

				
					az postgres server start --resource-group <resource-group-name> --name <server-name>

Note: Replace <resource-group-name> and <server-name> with the names of your resource group and PostgreSQL server, respectively.

awscli Command to Find PostgreSQL rds Free and Used Space

by

To determine the free and used disk space of a PostgreSQL RDS instance using the AWS CLI, you can use the describe-db-instances command and filter the output to show the FreeStorageSpace and AllocatedStorage fields:

				
					aws rds describe-db-instances --query 'DBInstances[*].{ID:DBInstanceIdentifier, Free:FreeStorageSpace, Allocated:AllocatedStorage}' --output table

This will give you a table with the ID of the RDS instance, the Free disk space in GB, and the Allocated disk space in GB. You can use these values to calculate the used disk space by subtracting the free disk space from the allocated disk space.

How to Resize PostgreSQL rds Disk Size Using awscli commands

by

To resize the disk size of a PostgreSQL RDS instance using the AWS CLI, you need to follow these steps but before that try to get the used and free space by running these commands from my artical

1.First, stop the RDS instance.

				
					aws rds stop-db-instance --db-instance-identifier <instance_name>

Modify the RDS instance’s disk size using the modify-db-instance command:

				
					aws rds modify-db-instance --db-instance-identifier <instance_name> --allocated-storage <new_size_in_gb>

start the instance

				
					aws rds start-db-instance --db-instance-identifier <instance_name>
Note: The size change will only occur when you stop and start the instance. Also, back up your data before performing any disk size changes.

Copy Table From one PostgreSQL RDS to Another using Python Boto3

by

To copy a table from one PostgreSQL RDS instance to another using Python and boto3, you will need to:

  1. Install the Python library psycopg2, which is a PostgreSQL adapter for Python. You can do this by running pip install psycopg2 in your terminal.

  2. Import the necessary modules:​

				
					import boto3
import psycopg2

  1. Connect to both the source and target RDS instances using the psycopg2 library. You will need to specify the host, port, database name, username, and password for each RDS instance.

  2. Once you have established connections to both RDS instances, you can use the psycopg2 library to create a cursor object for each connection. A cursor allows you to execute PostgreSQL commands and retrieve data.

  3. Use the cursor to execute a SELECT statement on the source table. This will retrieve all of the rows from the table.

  4. Iterate over the rows returned by the SELECT statement, and use the cursor for the target RDS instance to execute an INSERT statement for each row. This will copy the data from the source table to the target table.

Here is some sample code that demonstrates how to do this:

				
					# Connect to the source RDS instance
conn1 = psycopg2.connect(host="source-rds-instance.abc123.us-east-1.rds.amazonaws.com", port=5432, database="mydatabase", user="myuser", password="mypassword")
cur1 = conn1.cursor()

# Connect to the target RDS instance
conn2 = psycopg2.connect(host="target-rds-instance.abc123.us-east-1.rds.amazonaws.com", port=5432, database="mydatabase", user="myuser", password="mypassword")
cur2 = conn2.cursor()

# Select all rows from the source table
cur1.execute("SELECT * FROM mytable")

# Iterate over the rows and insert them into the target table
for row in cur1:
    cur2.execute("INSERT INTO mytable VALUES (%s, %s, %s)", row)

# Commit the changes to the target RDS instance
conn2.commit()

# Close the cursors and connections
cur1.close()
conn1.close()
cur2.close()
conn2.close()

I hope this helps! Let me know if you have any questions.

How to Take PostgreSQL Schema Backup

by

In some cases, we have to take PostgreSQL schema level backup. it’s like a logical backup where it just backup the schema objects like tables, indexes, and procedures. These backups are used in the condition where you need to restore just one schema backup, not the whole database. For daily backup, you can create a shell or bash script and schedule it using crontab or windows scheduler.

Script For Schema Backup 

				
					#!/bin/bash
mv /tmp/db_list.log /tmp/db_list.log_old
psql -t -c "SELECT datname FROM pg_database WHERE datistemplate = false;" > /tmp/db_list.log ; sed -i '/^$/d' /tmp/db_list.log
file=/tmp/db_list.log
#schema_name
#DB
date=$(date '+%Y-%m-%d')
bkp_path=/var/lib/pgsql

for DB in `cat $file`
do
echo "#################Starting the backup as on ${date} for ${DB}"
pg_dump -U postgres -v -n '*' -N 'pg_toast' -N 'information_schema' -N 'pg_catalog' $DB -Ft -f ${bkp_path}/${DB}_schema_bkp_${date}.tar > `${bkp_path}/${DB}_schema_bkp_${date}.log)`
echo "#################Backup Complited as on ${date} for ${DB}"
done

echo "listing files older than 15 Days"
find ${bkp_path} -name "*schem*.tar" -type f -mtime +1 > ${bkp_path}/old_file_list.log
find ${bkp_path} -name "*.log" -type f -mtime +1 > ${bkp_path}/old_file_list.log

echo "deleting backup/log file older than 15 Days"
find ${bkp_path} -name "*.tar" -type f -mtime +15 > ${bkp_path}/old_file_list.log -delete

In this script, we have excluded system schemas like ‘information_schema’ and many more. Please remove ‘-N and schema Name if you need a backup system schema.’ It will start including the schema backup in the tar file.

How to execute the Script ?

				
					chmod 777 backup_schema.sh
./backup_schema.sh

Read More...

How to Fix Pg_ctl command not found

by

Sometimes after a fresh PostgreSQL install if we want to start or stop from PostgreSQL rather than controlling the PostgreSQL instance start and stop from the root. And when we run the pg_ctl from the command prompt as a PostgreSQL Linux user we see the error bash: pg_ctl: command not found… 

In this article, lets learn how to fix it

As PostgreSQL check your pg base directory 

 

And now add this PostgreSQL to the Linux path so that it’s always available. I would recommand to create a postgresql env. file so that it more easy to handle rather than messing with other systems runing on linux operating system.

				
					--Step 1 
-bash-4.2$ echo $PGDATA
/var/lib/pgsql/10/data

--Step 2
-bash-4.2$ echo "PATH=/usr/pgsql-10/bin:$PATH">>~/.postgre_10.profile

--Step 3
chmod 777 .postgre_10.profile

--Step 4 
. .postgre_10.profile

--Step 5
Check pg_ctl now

-bash-4.2$ pg_ctl --version
pg_ctl (PostgreSQL) 10.17
-bash-4.2$