fbpx

Database

How to start PostgreSQL on Windows

by

To start PostgreSQL on Windows, you can follow these steps:

  1. Open the PostgreSQL installation directory. By default, it is installed in “C:\Program Files\PostgreSQL{version}”.
  2. Navigate to the “bin” subdirectory.
  3. Double-click on the “pg_ctl.exe” file to open the PostgreSQL control panel.
  4. Select the PostgreSQL service you want to start from the drop-down menu.
  5. Click on the “Start” button to start the service.

Alternatively, you can start PostgreSQL from the command line:

  1. Open the command prompt.

  2. Navigate to the “bin” subdirectory of the PostgreSQL installation directory.

  3. Type the following command to start the PostgreSQL service:

    pg_ctl.exe start -D "C:\Program Files\PostgreSQL\{version}\data"

    Replace “{version}” with the version number of PostgreSQL you have installed.

          Hit Enter.

After starting PostgreSQL, you can connect to it using a PostgreSQL client such as psql, pgAdmin, or any other client of your choice.

How to Configure SSL on PostgreSQL

by
Configuring PostgreSQL with SSL involves several steps, including:

 

Generate SSL Certificate

  • The first step is to generate an SSL certificate that will be used to secure the communication between the client and the server.
  • This can be done using a tool such as OpenSSL.
  • For example, to generate a self-signed certificate, you can use the following command:
				
					openssl req -new -x509 -nodes -out server.crt -keyout server.key

This will generate a certificate and a private key file in the current directory.

  • Configure PostgreSQL Server

    • The next step is to configure the PostgreSQL server to use SSL.
    • Open the postgresql.conf file and uncomment the ssl line and set its value to on.
    • Also, uncomment the ssl_cert_file and ssl_key_file lines and set their values to the paths of the certificate and private key files generated in step 1.

  • Configure PostgreSQL Client

    • The PostgreSQL client must also be configured to use SSL to communicate with the server.
    • Open the pg_hba.conf file and add an entry for SSL connections.
    • For example:
				
					hostssl all all 0.0.0.0/0 md5 clientcert=1

This will allow SSL connections from any IP address using the md5 authentication method and require the client to provide a valid SSL certificate.

  • Restart PostgreSQL Server

    • After making the above changes, you need to restart the PostgreSQL server for the changes to take effect.

  • Test SSL Connection

    • To test the SSL connection, you can use the psql command-line tool with the -sslmode option set to require.
    • For example:
				
					psql -h <hostname> -U <username> -d <database> -sslmode=require

This will establish an SSL-encrypted connection to the PostgreSQL server.

Note: It’s important to keep the SSL certificate and private key files secure and to configure the server and client to use strong encryption protocols and ciphers.

Enable SSL in PostgreSQL client

Configure following three files on ~/.postgresql/ directory

root.crt (trusted root certificate)
postgresql.crt (client certificate)
postgresql.key (private key)

genarte  postgresql.key on client side machine and purge the passphrase.

$ openssl genrsa -des3 -out /tmp/postgresql.key 1024

$ openssl rsa -in /crt/postgresql.key -out /crt/postgresql.key

genrate postgresql.crt, and sign it with using the trusted root (private key file from the database server). Please note, when you are prompted for certificate common name (CN), set it to database name.

$ openssl req -new -key /tmp/postgresql.key -out /tmp/postgresql.csr
$ openssl x509 -req -in server.req -out /tmp/postgresql.csr -CA root.crt -CAkey server.key -out /tmp/postgresql.crt -CAcreateserial

Copy the three files created in server’s /tmp directory to client machine. 

Copy root.crt from server /tmp directory to client’s ~/.postgresql/ directory.

Once all  is done connect to database from client 

and on database check if you are able to see the connection with ssl = True (T) using floowing quesry 

				
					SELECT datname,usename, ssl, client_addr 
  FROM pg_stat_ssl
  JOIN pg_stat_activity
    ON pg_stat_ssl.pid = pg_stat_activity.pid;

How to Become an Efficient, Prompt and Successful Database Administrator (DBA)

by

Becoming an efficient, prompt, and successful database administrator requires a combination of technical skills, problem-solving abilities, and soft skills. Here are some steps you can take to improve your skills and achieve your goals:

Stay up-to-date with industry trends: Keep abreast of the latest developments in the database management field. Attend conferences, read industry blogs and articles, and take online courses to gain a deeper understanding of new technologies and best practices.

Develop strong technical skills: Familiarize yourself with different database management systems, such as Oracle, Microsoft SQL Server, MySQL, and PostgreSQL. Additionally, acquire expertise in SQL, data modeling, and backup and recovery techniques.

Improve problem-solving skills: Database administrators often face complex problems that require quick thinking and effective decision-making. To improve your problem-solving skills, practice critical thinking and analysis techniques, and seek out opportunities to work on challenging projects.

Enhance communication skills: Effective communication is essential for successful database administration. Be clear and concise when communicating with stakeholders, and be prepared to explain technical concepts in non-technical terms.

Build strong teamwork skills: Collaborate with other members of your organization, including developers, network administrators, and security specialists, to ensure the smooth operation of your database systems.

Be proactive: Regularly perform preventive maintenance tasks, such as monitoring database performance, checking for disk space availability, and verifying backup and recovery processes.

Develop a positive attitude: Embrace challenges and remain calm under pressure. Being able to handle stressful situations in a positive manner can help you maintain a good work-life balance and avoid burnout.

By following these steps, you can become an efficient, prompt, and successful database administrator and make a valuable contribution to your organization.

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.