Cheetsheet for PostGresql learning

psql.sh

# For Restoring the the backup in the given database
# While running on docker I first copied the local file to docker container and docker cp and did below
psql -U {db_user} -d database_name -f {db}.sql

To create a user, a database, and then grant the user permissions to the database in MySQL or PostgreSQL, you'll need administrative access to your database server. The process is slightly different for each database management system. Here's how you can do it in both MySQL and PostgreSQL.

For MySQL

1. Login to MySQL as an admin:

mysql -u root -p

2. Create a new database:

Replace your_database with the name of your database.

CREATE DATABASE your_database;

3. Create a new user:

Replace your_user with the name of your user and your_password with a strong password.

CREATE USER 'your_user'@'localhost' IDENTIFIED BY 'your_password';

4. Grant privileges to the user on the database:

GRANT ALL PRIVILEGES ON your_database.* TO 'your_user'@'localhost';

5. Flush the privileges to ensure that the current instance of MySQL knows about the recent changes:

FLUSH PRIVILEGES;

6. Exit MySQL:

EXIT;

For PostgreSQL

1. Login to PostgreSQL as an admin:

sudo -u postgres psql

2. Create a new database:

Replace your_database with the name of your database.

CREATE DATABASE your_database;

3. Create a new user:

Replace your_user with the name of your user and your_password with a strong password.

CREATE USER your_user WITH ENCRYPTED PASSWORD 'your_password';

4. Grant privileges to the user on the database:

GRANT ALL PRIVILEGES ON DATABASE your_database TO your_user;

5. Exit PostgreSQL:

\q

Remember to replace your_database, your_user, and your_password with your actual database name, user name, and password. Also, depending on your specific requirements, you might want to customize the privileges granted to the user.

sudo docker exec -e PGPASSWORD="" -t {docker_name} pg_dump -U postgres {db} > backuptody.sql

Changing the Password

ALTER USER your_user WITH PASSWORD 'new_password';