PostgreSQL
On this occasion, I think it is very important to share a series of tutorials about the administrator of the PostgreSQL database. Because in my opinion it is one of the best database administrators in relation to relational DB. In this first part of the tutorial, I would like to focus more than anything on the first steps to start with this important tool. Teaching the installation and some features that will help people get started. As I go through the whole series, I will teach more complex functionalities that will help with the real development of a project. I hope you like it.
PostgreSQL is a relational database manager, PostgreSQL's source code is available under a liberal open source license, is considered one of the most advanced database managers today; the first version of the code was public on August 1, 1996, released under the PostgreSQL License and developed by PGDG "PostgreSQL Global Development Group". Now are in your version 10.3 but for this tutorial we will used the version 9.6, It has support for different OS like:
In this opportunity we will use the operating system: Debian Jessie 8.
wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -
Second, as sudo (superuser) , you can update with
sudo apt-get update
Third, you can to install as sudo:
sudo apt-get install postgresql-9.6
You can use the following command as root: # su postgres and after psql postgres
After, you can to change the password with:
postgres=# ALTER ROLE postgres PASSWORD 'new';
You must create the user as root:
#su postgres
createuser -D -S -R -l new_user
After, you can connect with the server PostgreSQL to enter the password of created user:
psql postgres
We check the user created:
postgres=# SELECT usename, passwd FROM pg_shadow;
postgres=# ALTER USER new_user WITH ENCRYPTED PASSWORD 'newpass';
#su postgres
createdb -Ttemplate0 -O new_user -EUTF-8 newdatabase
postgres@erickaackseriam:~$ psql postgres
postgres=# SELECT datname FROM pg_database;
postgres=# \q
First, you must enter as root:
#su postgres
After, you can connect with the server PostgreSQL and Choose the database
psql -d newdatabase -U new_user -W
Then, you can create the table with the following command
CREATE TABLE person (
id SERIAL PRIMARY KEY NOT NULL,
name varchar(30) NOT NULL,
password varchar(30) NOT NULL,
email varchar(30) NOT NULL);
In this case the table will have the name "user", in the parentheses are the columns of the table, then you enter the datatype (varchar, int). In this case:
\dt
First, you must enter as root:
#su postgres
After, you can connect with the server PostgreSQL and Choose the database
psql -d newdatabase -U new_user -W
Then, remove the table with the following command
DROP TABLE person;
First, we enter as root:
#su postgres
After, you can connect with the server PostgreSQL and Choose the database
psql -d newdatabase -U new_user -W
You can execute the registration with INSERT INTO. With this operation, you can insert a single row .
INSERT INTO person (name, password,email)
VALUES ('ana', '123','[email protected]');
#su postgres
psql -d newdatabase -U new_user -W
UPDATE person SET password='updatepass'
where email='[email protected]';
-We check the changes with
SELECT * FROM person;
You can use this SQL to remove the data in the table:
(Where email is "[email protected]")
DELETE FROM person where email='[email protected]';
(For example for all the privileges of the table)
GRANT ALL PRIVILEGES ON DATABASE newdatabase TO new_user;