Postgresql Installation and Management Basics2022-07-21
PostgreSQL or simply postgres is the most advanced, SQL-compliant and open-source objective-RDBMS. In This Article, you will learn how to Install, Connect and Manage a postgres database.
PostgreSQL or simply postgres is the most advanced, SQL-compliant and open-source objective-RDBMS. Compared to other RDBMSs, PostgreSQL differs itself with its support for reliable transactions, i.e. Atomicity, Consistency, Isolation, Durability (ACID).In This Article, you will learn how to Install, Connect and Manage a postgres database.
Installing & Managing postgresql Database in Debian:
sudo apt-get install postgresql-9.5 libpq-dev
This will install Database server, its dependancy packages and Library Files. This will also create a default user for postgresql called postgres.
Connecting to Database & initial setup:
# connecting to database sudo su su postgres -c "psql" # change postgres user password ALTER USER postgres PASSWORD 'our-new-password'; \q # modify pg_hba.conf to connect to database with password only. local all postgres peer local all all peer change peer to md5 and restart postgresql for changes to take affect. # connecting to database psql -U postgres
enter password when prompted and you will be connected to databse.
# create new database CREATE DATABASE gondor; # create new user CREATE USER elessar WITH PASSWORD '<some-password>' # grant the user permission to Database GRANT ALL PRIVILEGES ON DATABASE gondor TO elessar; # switch to database connect <database-name>
Using Docker Image:
wget -qO- https://get.docker.com/ | s
Deploy postgres container:
docker run --name some-postgres -e POSTGRES_PASSWORD=<password-postgres-user> -d postgres
Replace <password-postgres-user> and run the command. This will launch postgresql database.
Connecting to this Docker Instance:
apt-get install postgresql-client-common postgresql-client-9.5
This will install client tools, then you can connect using
psql -U postgres -h <docker-ip>
This will prompt for password and then you can connect and use postgres as you normally would.