PostgreSQL is quite popular, open source object-relational database system with over 30 years of active development that has earned it a strong reputation for reliability, feature robustness, and performance.
You can download PostgreSQL from https://www.postgresql.org/
When you install it you will get
Here is a good tutorial for PostgreSQL http://www.postgresqltutorial.com/
In Windows you can configure how the PostgreSQL starts with below commands
The following command connects to a database under a specific user. After pressing Enter PostgreSQL will ask for the password of the user.
psql -d database -U userIf you want to connect to a database that resides on another host, you add the -h option as follows:
psql -h host -d database -U userSwitch connection to a new database
\c dbname usernameList available databases
\lList available tables
\dtList available schema
\dnExecute the previous command
\gGet help on psql commands
A database can contain one or multiple schemas while each schema belongs to only one database. Two schemas can have different objects that share the same name. PostgreSQL creates a schema named public for every new database. Read more about schemas from http://www.postgresqltutorial.com/postgresql-schema/
The default administrator is postgres. You have to first login with that user and then you can create new users and give privileges to them.
Example: Create a new user libsuer and give privileges to all tables in database library to that user.
CREATE ROLE libuser WITH LOGIN PASSWORD 'libpass';And then you can give permissions to that user, like this
\c libary GRANT ALL PIVILEGES ON TABLE book TO libuser; or GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO libuser;
pg_dump works mostly the same way as myslq_dump. So you can start it from the command line and backup a database like this
pg_dump -U user database > db.sqlThen you can restore the database with this command
psql -U user -d database < db.sqlNote: In Windows pg_dump and psql are located in ProgramFiles\PostgreSQL\...\bin
When you have installed PostgreSQL, you probably have also installed pgAdmin, which is a quite handy PostgreSQL-client. If you don't have it yet, you can get it from https://www.pgadmin.org
DBeaver is also quite handy graphical SQL-client. And infact, you can use it also with MySQL and SQL-server. You can download it from https://dbeaver.io/.
Login to your PostgreSQL as administrator (=postgres) and execute below commannds
CREATE DATABASE test; \c test; CREATE ROLE user1 WITH LOGIN PASSWORD 'user1'; CREATE ROLE user2 WITH LOGIN PASSWORD 'user2'; CREATE SCHEMA schema1; CREATE SCHEMA schema2; CREATE TABLE schema1.table1( idTable1 INT primary key, name CHAR(50)); CREATE TABLE schema2.table2( idTable2 INT primary key, name CHAR(50)); insert into schema1.table1 VALUES (1,'Jim'); insert into schema2.table2 VALUES (1,'Lisa'); GRANT USAGE ON SCHEMA schema1 to user1; GRANT USAGE ON SCHEMA schema2 to user2; GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA schema1 to user1; GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA schema2 to user2;Then login as a user1 (psql -U user1 -d test) and execute below commands. Some of them does not work. Try to figure out why.
\dt SELECT * FROM table1; SELECT * FROM schema1.table1; SELECT * FROM schema2.table2; set search_path to schema1; \dt SELECT * FROM table1;Then you can delete the database and user1 and user2.
user1 and user2 should connect out as a administrator \c test DROP TABLE schema1.table1; DROP SCHEMA schema1; DROP USER user1; DROP TABLE schema2.table2; DROP SCHEMA schema2; DROP USER user2; \c postgres DROP DATABASE test