1.  How to install PostgreSQL on pcDuino Ubuntu

http://learn.linksprite.com/pcduino/linux-applications/how-to-install-postgresql-on-pcduino-ubuntu/

2. Login to PostgreSQL

After completing step 1 , we have installed postgres 9.3.4 on pcDuino Ubuntu , Let do a basic test to verify that installation completed successfully. To verify switch to postgres user.

# su - postgres

Use psql command to access PostgreSQL prompt with admin privileges.

$ psql

psql (9.3.4)
Type "help" for help.

postgres=#

Congratulation’s! You have successfully Login to PostgreSQL.

3. Create postgres database

postgres@ubuntu:~# createdb -U postgres pcDuino

[OR]

(root)# createdb -U postgres pcDuino

[OR]

postgres=# CREATE DATABASE pcDuino

4. List all databases( ” \l ” (It’s not number 1))

postgres=# \l

5. Drop database

postgres=# DROP DATABASE db1;

[OR]

$ dropdb mydb

6. Connect to database

postgres=# \c  pcDuino

7. Create table in connected database

pcDuino=# CREATE TABLE tb07  (name VARCHAR(20), tb7_id INT, tb7_name VARCHAR(100), tb7_date DATE);

8. List tables in connected database

postgres=# \dt

9. List columns on table

pcDuino=# \d tb07

10. Add columns in existing table

pcDuino=# ALTER TABLE tb07 ADD tb7_address VARCHAR(400);

11. Delete column from table

db1=#  ALTER TABLE tb07 drop tb7_address;

12. Create role in postgres

postgres=# CREATE ROLE role1 WITH LOGIN ENCRYPTED PASSWORD 'password' CREATEDB;

13 . List roles in postgres

postgres=# \du

14. Grant privileges to role on database

postgres=# GRANT ALL PRIVILEGES ON DATABASE db1 TO role1;

15. Alter specific role

postgres=# ALTER ROLE role1 CREATEROLE CREATEDB SUPERUSER;

Click here to get list of roles in postgresql.

16. Drop specific role

postgres=# DROP ROLE role1;

17. Backup database

$ pg_dump db1 > backup.sql

18. Restore database

$ psql db1 < backup.sql

19. Backup all databases

$ pg_dumpall > pgbackup.sql

20. Show all runtime parameters of database

postgres=# SHOW ALL;