PostgreSQL

Configuration:

1. Access psql:

    sudo su - postgres
   

   1. Alternatively

       psql postgres
      

2. Reset password for psql postgres user:

    sudo su postgres -c psql template1
    postgres=# ALTER USER postgres WITH PASSWORD 'topsecret';
   

3. Reset password for unix postgres user and enter the same password as above:

    sudo passwd -d postgres
    sudo su postgres -c passwd
   

4. Start a postgres server:

    sudo postgresql start
   

5. Location of pg_hba.conf on ubuntu

     /etc/postgresql/9.2/main/pg_hba.conf
   

6. Location of pg_hba.conf on the mac

    /Library/PostgreSQL/9.1/data/pg_hba.conf
   

7. Using postgres client only

    sudo apt-get install libpq-dev
   

8. On the mac, set the following under .bash_profile:

    export PGHOST=localhost
   

9. For the following error message:

    could not connect to server: Connection refused
    Is the server running on host "192.168.23.129" and accepting
    TCP/IP connections on port 5432?
   

   Edit /etc/postgresql/9.2/main/postgresql.conf and add followed by a restart:

    listen_addresses = '*'
   

10. Connecting to SQLAlchemy without providing username or password:

     SQLALCHEMY_DATABASE_URI = 'postgresql:///somedatabase'
    

11. Uninstall and older version on the mac:

     open /Library/PostgreSQL/9.2/uninstall-postgresql.app
    

    Uninstall psycopg2 and reinstall it after upgrading postgres.

12. Updating the port number on mac:

     /etc/postgres-reg.ini
     /Library/PostgreSQL/9.4/data/pg_hba.conf
     /Library/PostgreSQL/9.4/data/postgresql.conf
    

Other actions

1. Fast restart

    pg_ctl restart -m fast
   

2. Reloading after making a change to pg_hba.conf

    pg_ctl reload -D /Library/PostgreSQL/9.1/data/
   

   or

    SELECT pg_reload_conf();
   

3. FATAL: could not create shared memory segment: Cannot allocate memory on OS X 10.6

    sudo sysctl -w kern.sysv.shmall=65536   #  Also add in /etc/sysctl.conf
   

4. Slow performance on the mac, consider increasing

    kern.sysv.shmmax=1610612736  # max seg size
    kern.sysv.shmall=393216      # max total shared memory
   

5. Backup entire database:

    pg_dump name_of_database > name_of_backup_file
   

6. Backup the data of a specific table some_table:

    pg_dump -a -t some_table > /tmp/some_table.sql
   

7. Backup the schema of a specific table some_table:

    pg_dump --host localhost --port 5432 --username "postgres" --role "postgres" --schema-only --file "some_table.sql" --table "some_table" "the_database_name"
   

From a psql shell:

1. List databases:

    \l
   

2. Connect to a database:

    \c database_name
   

3. List tables in a database:

    \d
   

4. Show table columns:

    \d table_name
   

5. Restoring a dump. Create the database as postgres and change the owner to app user:

    ALTER DATABASE local_database_dev OWNER TO new_owner;
   

   pass the database name and dump filename

    psql local_database_dev < dump.sql
   

6. Check the size of a database

    select pg_size_pretty(pg_database_size('dbname'));
   

7. Run SQL script in psql

    \i example.sql
   

8. List all table that starts with a specific prefix

    SELECT tablename FROM pg_tables WHERE tablename LIKE 'product%';
   

9. Checks user permission

    SELECT * FROM pg_user;
   

10. Give user CREATEDB permission

     ALTER USER tlam CREATEDB;
    

11. Grant permission, after connecting to a database:

     GRANT ALL ON ALL SEQUENCES IN SCHEMA public TO tlam
     GRANT ALL ON ALL TABLES IN SCHEMA public TO tlam
    

12. Display a procedure

     \df+ the_procedure
    

13. Show procedure source code

      \ef the_procedure
    

14. Check current queries

     SELECT query from pg_stat_activity;
    

15. View triggers

     SELECT tgname from pg_trigger;
    

16. Filter on an attribute in a jsonb column called data:

     SELECT *
     FROM table
     WHERE data->>'some_attribute' = 'some value;
    

17. Copying data from a table to a csv and back to another table:

     # In staging server
     COPY country TO ~/country.csv WITH (FORMAT csv);
    
     # Local db
     COPY country FROM ~/country.csv WITH (FORMAT csv);
    

Schemas

1. List all schemas

    \dn
   

2. Check the current search path, it might default to public

    SHOW search_path;
   

3. Switch the search_path to a specific schema

    SET search_path TO other_schema
   

Adding new user and db setup

1. Create a new user with CREATEDB permission:

    CREATE USER nba_user WITH PASSWORD 'nba' CREATEDB;
   

2. Give peer authentication to nba_user by adding the following line to /etc/postgresql/9.2/main/pg_hba.conf:

    # TYPE  DATABASE        USER            ADDRESS                 METHOD        
    local   all             nba_user        trust
   

   Reload the database: sudo /etc/init.d/postgresql reload

Mac GUIs

• https://www.macpostgresclient.com/
• https://tableplus.com/

Reference