How to increase memory in PostgreSQL - Query

 

CONFIGURATION - Memory Related Parameters

==============CONFIGURATION==========================

-bash-4.1$ cd data/

-bash-4.1$ pwd

/opt/PostgreSQL/10/data

-bash-4.1$ vi postgresql.conf 

shared_buffers=256MB

:wq

-bash-4.1$ pg_ctl -D ../data/ restart

server signaled

-bash-4.1$ psql -p 5432 -c "show shared_buffers;"

Password: 

 shared_buffers 

----------------

 256MB

(1 row)

-bash-4.1$ vi postgresql.conf 

work_mem=10MB

:wq

-bash-4.1$ pg_ctl -D ../data/ reload

server signaled

-bash-4.1$ psql -p 5432 -c "show work_mem;"

Password: 

 work_mem 

----------

 10MB

(1 row)

-bash-4.1$ psql -p 5432

Password: 

psql.bin (10.9)

Type "help" for help.

postgres=# \du

                                   List of roles

 Role name |                         Attributes                         | Member of 

-----------+------------------------------------------------------------+-----------

 edbstore  |                                                            | {}

 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}

postgres=# set work_mem to '100MB';

SET

postgres=# show work_mem;

 work_mem 

----------

 100MB

(1 row)

postgres=# \c

You are now connected to database "postgres" as user "postgres".

postgres=# show work_mem;

 work_mem 

----------

 10MB

(1 row)

postgres=# alter user edbstore set work_mem to '100MB';

ALTER ROLE

postgres=# \c postgres edbstore

Password for user edbstore: 

You are now connected to database "postgres" as user "edbstore".

postgres=> show work_mem;

 work_mem 

----------

 100MB

(1 row)

postgres=# \c  edbstore 

You are now connected to database "edbstore" as user "postgres".

edbstore=# \dn

   List of schemas

   Name   |  Owner   

----------+----------

 accounts | u02

 hr       | u01

 public   | postgres

(3 rows)

edbstore=# \c edbstore u01

You are now connected to database "edbstore" as user "u01".

edbstore=> show search_path;

   search_path   

-----------------

 "$user", public

(1 row)

edbstore=> \c edbstore postgres

You are now connected to database "edbstore" as user "postgres".

edbstore=# alter user u01 set search_path to hr;

ALTER ROLE

edbstore=# \c edbstore u01

You are now connected to database "edbstore" as user "u01".

edbstore=> show search_path;

 search_path 

-------------

 hr

(1 row)

edbstore=> 

edbstore=# select * from pg_shadow;

 usename  | usesysid | usecreatedb | usesuper | userepl | usebypassrls |               passwd                | valuntil |    useconfig     

----------+----------+-------------+----------+---------+--------------+-------------------------------------+----------+------------------

 postgres |       10 | t           | t        | t       | t            |                                     |          | 

 u01      |    16411 | f           | f        | f       | f            | md5b8f200a2ca29c977c85af06ed81fde21 |          | {search_path=hr}

 u02      |    16412 | f           | f        | f       | f            | md5c764c50c8397ef7afa316dd36a71cb44 |          | 

(3 rows)

edbstore=# 

edbstore=# alter system set shared_buffers to '512MB';

ALTER SYSTEM

edbstore=# show shared_buffers;

 shared_buffers 

----------------

 128MB

(1 row)

edbstore=# \q

-bash-4.1$ psql -p 5433

psql.bin (10.9)

Type "help" for help.

postgres=# show data_directory;

  data_directory  

------------------

 /home/prod

(1 row)

postgres=# \q

-bash-4.1$ pg_ctl -D /home/prod/ restart

-bash-4.1$ psql -p 5433

postgres=# show shared_buffers;

 shared_buffers 

----------------

 512MB

(1 row)

postgres=# 

postgres=# select name,setting,sourcefile from pg_settings where name like 'shared_buffers%';

      name      | setting |              sourcefile               

----------------+---------+---------------------------------------

 shared_buffers | 65536   | /home/prod/postgresql.auto.conf

(1 row)

postgres=# 

============CASE STUDY============================

1. Open psql and write a statement to change work_mem to 10MB. This change must persist across server restarts

-bash-4.1$ vi postgresql.conf 

work_mem=10MB

:wq

-bash-4.1$ pg_ctl -D ../data/ reload

server signaled

-bash-4.1$ psql -p 5432 -c "show work_mem;"

Password: 

 work_mem 

----------

10MB

2. Open psql and write a statement to change work_mem to 20MB for the current session

set work_mem to '20MB';

3. Open psql and write a statement to change work_mem to 1 MB for the postgres user

alter user edbstore set work_mem to '1MB';

4. Write a query to list all parameters requiring a server restart

select name,applie from pg_file_settings;

5. Open the configuration file for your Postgres database cluster and make the following changes

    

    Maximum allowed connections to 50

    max_connections = 50

    Authentication time to 10 mins

    authentication_timeout=10m default is 1m 

    Shared buffers to 256 MB

    shared_buffers=256MB

    work_mem to 10 MB

    work_mem=10MB

    wal_buffers to 8MB

    wal_buffers=8MB