• Author / Uploaded
• Victor Polindara

• Categories
• Sistema operativo
• Postgre Sql
• Memoria de acceso aleatorio
• Microprocesador
• Bases de datos

Citation preview

Configuración y Gestión del Sistema Manejador de Bases de Datos

Ramiro Polindara Montenegro. Agosto 2019. Servicio Nacional de Aprendizaje. Ficha de Caracterización 1881764. Gestión y Seguridad de Bases de Datos

Tabla de Contenidos Capítulo 1 Introducción . .................................................................................................... 1 Capítulo 2 Objetivos . ......................................................................................................... 2 Capítulo 3 Configuracion del SMBD PostgreSql….……………………………………..3 Especificaciones de Software…………………………………………………………3 Especificaciones de Hardware.………………………………………………………..4 Capítulo 4 Afinamiento del SMBD PostgreSQL..………………….……………….……6 Capítulo 5 Manejo y Operación del SMBD PostgreSQL ………………………………..30 Herramienta PgAdmin y SQL S….hell.……………………………… ………….…30 Administración de Servicios…………………………………………………………43 Administración del SMBD desde SQL Shell……..………..………………………...45

ii

1 Capítulo 1 Introducción En la configuración del sistema manejador de bases de datos de debe tener en cuenta el sistema operativo sobre el cual esta montado el SMBD, las características técnicas del equipo que soporta el sistema, siendo el procesador y la memoria RAM dos elementos que dependiendo de sus capacidades y configuración que se le haga, nos permitirá lograr un mejor desempeño y rendimiento de las bases de datos. De igual manera se debe abordar con mucha experticia y conocimiento técnico la configuración de la base de datos, los tablespace, los roles de los usuarios, la creación de las tablas.

2

Capítulo 2 Objetivos 1. Determinar las características técnicas del equipo donde se instala el sistema manejador de bases de datos. 2. Afinar los archivos de configuración del sistema de bases de datos de acuerdo a la memoria RAM del equipo, de igual manera teniendo en cuenta las actividades a desarrollar en la base de datos como la concurrencia de usuarios. 3. Aprender a administrar las bases de datos con comandos de consola desde SQL Shell y desde la interfaz gráfica pgAdmin.

3 Capítulo 3 Configuración del Sistema Manejador de Bases de Datos PostgreSql Especificaciones de software SISTEMA OPERATIVO Windows

Sistema Operativo

PgAdmin4

VERSION S.O. 10 Pro

VERSION SMBD PostgreSQL 11

HERRAMIENTA DE ADMINISTRACION GUI pgAdmin 4

4

PostgreSQL

Especificaciones de Hardware Descripción del Equipo donde se Instaló el Software Tipo: Portátil Marca: Toshiba Modelo: Satellite Procesador: AMD FX-8800P Radeon R7, 12 Compute Cores 4C+8G Arquitectura del Procesador: 64 bits Velocidad del Reloj del Procedor: 2,10 Ghz Memoria Cache del Procedor: L1 512 kB L2 4.0 MB

Procesador

5

Memoria cache

6 Capítulo 4 Afinamiento del Sistema Manejador de Bases de Datos PostgreSql Archivos de configuración Archivo pg_hba.conf En este archivo se configura los diferentes tipos de acceso que los usuarios tienen a las bases de datos, también muestra el host o dirección IP donde se encuentra la base de datos. Se debe agregar la siguiente linea host template1 postgres 192.168.0.0/24 md5 Esto se debe hacer para que permita crear el Servidor, esta línea es par una red IPV4.

Ubicación: C:\DataPostgreSQL # TYPE DATABASE

USER

ADDRESS

# IPv4 local connections: host all all 127.0.0.1/32 host template1 postgres 192.168.0.0/24 md5

METHOD

md5

Archivo pg_ident.conf Ubicación: C:\DataPostgreSQL No se le realizo modificación De acuerdo con la guía especifica que define la información necesaria en el caso que utilicemos un acceso del tipo ident en pag_hba.conf.

Archivo postgresql,conf Ubicación: C:\DataPostgreSQL No se le realizo modificación Almacena la información respecto al puerto de conexión de la base de datos, el número máximo de conexiones, el tamaño del buffer de memoria utilizado por postgresql, el cual no debe ser mayor a la 3 parte de la memoria del servidor.

7 Modificación Archivo postgresql,conf PARAMETRO max_connections shared_buffers

work_mem

maintenance_work_mem

effective_cache_sise

checkpoint_segments

max_stack_depth

ASPECTOS U OPERACIONES QUE DETERMINAN EL VALOR Cantidad de usuarios que se conectan a la base de datos, valor por defecto 100 Tamaño buffer de memoria utilizado por postgresql, no debe superar la tercera parte de la ram del equipo, valor por defecto 128 MB. El 25% de 16 GB del equipo son 4 GB Define la cantidad de memoria (MB) asignada a operaciones sql de la clausulas ORDER BY, DISTINCT, JOINS, etc., se puede tomar entre el 2% y 4 % del total de la RAM. 2 % de 16 GB Cantidad de memoria asignada a operaciones de tipo VACUUM, ANALIZE, CREATE INDEX, ALTER TABLE Y ADD FOREING KEY, el valor depende del tamaño de las bases de datos, se puede asignar 1/16 de la ram. 16 GB RAM se toma 1/16 corresponde a 1GB es decir 1024 MB, valor por defecto 64MB Parámetro usado por el palnificador de consultas query planner de PostgreSQL para optimizar la lectura de datos. Se puede asignar un mínimo del 50% y un máximo del 66% de la RAM disponible. 50% 16 GB son 8 GB que corresponde a 8192 mb Parámetro usado para indicar a PosgreSQL un promedio de operaciones de escritura INSERT, UPDATE Y DELETE. Cuando se tiene pocas operaciones se le puede asignar 64 MB y se puede aumentar hasta 256 MB. Especifica el tamaño máximo de profundidad de la pila de ejecución del servidor. Valor por defecto 2 MB. Postgresql lo utiliza cuando se ejecutan procesos almacenados (TRIGGER), operaciones complejas. La configuración ideal para este parámetro es el limite real de tamaño de pila impuesto por el kernel (como lo establece ulimit -s), restándole un megabyte para tener un margen de seguridad.

VALOR 150 MB 4096 MB

4 MB

1024 MB

8192 MB

128 MB

3 MB

8

Archivo postgresql,conf # ----------------------------# PostgreSQL configuration file # ----------------------------# # This file consists of lines of the form: # # name = value # # (The "=" is optional.) Whitespace may be used. Comments are introduced with # "#" anywhere on a line. The complete list of parameter names and allowed # values can be found in the PostgreSQL documentation. # # The commented-out settings shown in this file represent the default values. # Re-commenting a setting is NOT sufficient to revert it to the default value; # you need to reload the server. # # This file is read on server startup and when the server receives a SIGHUP # signal. If you edit the file on a running system, you have to SIGHUP the # server for the changes to take effect, run "pg_ctl reload", or execute # "SELECT pg_reload_conf()". Some parameters, which are marked below, # require a server shutdown and restart to take effect. # # Any parameter can also be given as a command-line option to the server, e.g., # "postgres -c log_connections=on". Some parameters can be changed at run time # with the "SET" SQL command. # # Memory units: kB = kilobytes Time units: ms = milliseconds # MB = megabytes s = seconds # GB = gigabytes min = minutes # TB = terabytes h = hours # d = days

#-----------------------------------------------------------------------------# FILE LOCATIONS #-----------------------------------------------------------------------------# The default values of these variables are driven from the -D command-line # option or PGDATA environment variable, represented here as ConfigDir. #data_directory = 'ConfigDir' directory

# use data in another

9

#hba_file = 'ConfigDir/pg_hba.conf'

# (change requires restart) # host-based authentication

#ident_file = 'ConfigDir/pg_ident.conf'

# (change requires restart) # ident configuration file

file

# (change requires restart) # If external_pid_file is not explicitly set, no extra PID file is written. #external_pid_file = '' # write an extra PID file # (change requires restart)

#-----------------------------------------------------------------------------# CONNECTIONS AND AUTHENTICATION #-----------------------------------------------------------------------------# - Connection Settings listen_addresses = '*' listen on;

# what IP address(es) to

# comma-separated list of addresses; # defaults to 'localhost'; use '*' for all

port = 5432 (change requires restart) max_connections = 150 requires restart) #superuser_reserved_connections = 3 #unix_socket_directories = '' directories

#unix_socket_group = '' requires restart) #unix_socket_permissions = 0777 octal notation

# (change requires restart) # #

(change

# (change requires restart) # comma-separated list of

# (change requires restart) # (change # begin with 0 to use

10

#bonjour = off advertise server via Bonjour

#bonjour_name = '' computer name

# (change requires restart) #

# (change requires restart) # defaults to the

# (change requires restart) # - TCP Keepalives # see "man 7 tcp" for details #tcp_keepalives_idle = 0 seconds;

#tcp_keepalives_interval = 0 in seconds;

#tcp_keepalives_count = 0

# TCP_KEEPIDLE, in

# 0 selects the system default # TCP_KEEPINTVL,

# 0 selects the system default # TCP_KEEPCNT; # 0 selects the system default

# - Authentication #authentication_timeout = 1min #password_encryption = md5

# 1s-600s # md5 or scram-sha-

256 #db_user_namespace = off # GSSAPI using Kerberos #krb_server_keyfile = '' #krb_caseins_users = off # - SSL #ssl = off #ssl_ca_file = '' #ssl_cert_file = 'server.crt' #ssl_crl_file = '' #ssl_key_file = 'server.key' #ssl_ciphers = 'HIGH:MEDIUM:+3DES:!aNULL' # allowed SSL ciphers #ssl_prefer_server_ciphers = on

11 #ssl_ecdh_curve = 'prime256v1' #ssl_dh_params_file = '' #ssl_passphrase_command = '' #ssl_passphrase_command_supports_reload = off

#-----------------------------------------------------------------------------# RESOURCE USAGE (except WAL) #-----------------------------------------------------------------------------# - Memory #shared_buffers = 128MB

# min 128kB # (change requires restart)

shared_buffers = 4096MB #huge_pages = try

#temp_buffers = 8MB #max_prepared_transactions = 0

# on, off, or try # (change requires restart) # min 800kB # zero disables the

feature # (change requires restart) # Caution: it is not advisable to set max_prepared_transactions nonzero unless # you actively intend to use prepared transactions. work_mem = 4MB # min 64kB maintenance_work_mem = 1024MB #autovacuum_work_mem = -1 maintenance_work_mem max_stack_depth = 3MB dynamic_shared_memory_type = windows

# min 1MB # min 1MB, or -1 to use # min 100kB # the default is the first option # supported by the operating

system: # posix # sysv # windows # mmap

12

# use none to disable dynamic shared memory # (change requires restart) # - Disk #temp_file_limit = -1 process temp file space

# limits per-

# in kB, or -1 for no limit # - Kernel Resources #max_files_per_process = 1000

# min 25 # (change requires restart)

# - Cost-Based Vacuum Delay #vacuum_cost_delay = 0 milliseconds #vacuum_cost_page_hit = 1 #vacuum_cost_page_miss = 10 #vacuum_cost_page_dirty = 20 #vacuum_cost_limit = 200

#

0-100

# 0-10000 credits # 0-10000 credits # 0-10000 credits # 1-10000 credits

# - Background Writer #bgwriter_delay = 200ms between rounds #bgwriter_lru_maxpages = 100 written/round, 0 disables #bgwriter_lru_multiplier = 2.0 buffers scanned/round #bgwriter_flush_after = 0 disables

# 10-10000ms #

max

buffers

# 0-10.0 multiplier on # measured in pages, 0

# - Asynchronous Behavior #effective_io_concurrency = 0 prefetching #max_worker_processes = 8 restart)

# 1-1000; 0 disables #

(change

requires

13 #max_parallel_maintenance_workers = 2 max_parallel_workers #max_parallel_workers_per_gather = 2 max_parallel_workers #parallel_leader_participation = on #max_parallel_workers = 8 max_worker_processes that

#

taken

from

#

taken

from

# maximum number of

# can be used in parallel operations #old_snapshot_threshold = -1 disables; 0 is immediate

#backend_flush_after = 0 disables

#

1min-60d;

-1

# (change requires restart) # measured in pages, 0

#-----------------------------------------------------------------------------# WRITE-AHEAD LOG #-----------------------------------------------------------------------------# - Settings #wal_level = replica replica, or logical

#fsync = on data to disk for crash safety

#

minimal,

# (change requires restart) # flush

# (turning this off can cause # corruption) #synchronous_commit = on level;

unrecoverable #

data

synchronization

# off, local, remote_write, remote_apply, or on #wal_sync_method = fsync option

# the default is the first

# supported by the operating system:

14

# open_datasync #

fdatasync (default on

Linux) # fsync # fsync_writethrough # open_sync #full_page_writes = on partial page writes #wal_compression = off compression of full-page writes #wal_log_hints = off page writes of non-critical updates

#wal_buffers = -1 sets based on shared_buffers

#wal_writer_delay = 200ms #wal_writer_flush_after = 1MB disables #commit_delay = 0 100000, in microseconds #commit_siblings = 5

# recover from #

enable

# also do full

# (change requires restart) # min 32kB, -1

# (change requires restart) # 1-10000 milliseconds # measured in pages, 0

#

range

0-

# range 1-1000

# - Checkpoints #checkpoint_timeout = 5min max_wal_size = 1GB min_wal_size = 80MB #checkpoint_completion_target = 0.5 0.0 - 1.0 #checkpoint_flush_after = 0 disables #checkpoint_warning = 30s

# range 30s-1d

# checkpoint target duration, # measured in pages, 0 # 0 disables

# - Archiving #archive_mode = off off, on, or always

# enables archiving;

15 # (change requires restart) #archive_command = '' archive a logfile segment

# command to use to #

placeholders: %p = path of file to archive # %f = file name only # 'test ! -f /mnt/server/archivedir/%f && cp %p /mnt/server/archivedir/%f' #archive_timeout = 0 # force segment switch after this

a

e.g. logfile

# number of seconds; 0 disables

#-----------------------------------------------------------------------------# REPLICATION #-----------------------------------------------------------------------------# - Sending Servers # Set these on the master and on any standby that will send replication data. #max_wal_senders = 10 walsender processes

#

max

number

of

# (change requires restart) #wal_keep_segments = 0 disables #wal_sender_timeout = 60s #max_replication_slots = 10

# in logfile segments; 0 # in milliseconds; 0 disables # max number of replication

slots # (change requires restart) #track_commit_timestamp = off transaction commit

#

collect

timestamp #

(change requires restart) # - Master Server # These settings are ignored on a standby server.

of

16 #synchronous_standby_names = '' sync rep

# standby servers that provide #

method to choose sync standbys, number of sync standbys, #

and

comma-separated list of application_name # from standby(s); '*' = all #vacuum_defer_cleanup_age = 0 cleanup is delayed

# number of xacts by which

# - Standby Servers # These settings are ignored on a master server. #hot_standby = on disallows queries during recovery

#max_standby_archive_delay = 30s

#

"off"

# (change requires restart) # max delay before canceling

queries # when reading WAL from archive;

#max_standby_streaming_delay = 30s

# -1 allows indefinite delay # max delay before canceling

queries # when reading streaming WAL;

#wal_receiver_status_interval = 10s

# -1 allows indefinite delay # send replies at least this

often

#hot_standby_feedback = off standby to prevent

#wal_receiver_timeout = 60s waits for

# 0 disables # send

from

# query conflicts # time that receiver

# master

info

communication

from

17

#wal_retrieve_retry_interval = 5s

# in milliseconds; 0 disables # time to wait before retrying

to # retrieve WAL after a failed attempt # - Subscribers # These settings are ignored on a publisher. #max_logical_replication_workers = 4 max_worker_processes

#max_sync_workers_per_subscription = 2 max_logical_replication_workers

#

#enable_bitmapscan = on #enable_hashagg = on #enable_hashjoin = on #enable_indexscan = on #enable_indexonlyscan = on #enable_material = on #enable_mergejoin = on #enable_nestloop = on #enable_parallel_append = on #enable_seqscan = on #enable_sort = on #enable_tidscan = on #enable_partitionwise_join = off #enable_partitionwise_aggregate = off #enable_parallel_hash = on #enable_partition_pruning = on # - Planner Cost Constants -

from

# (change requires restart) # taken from

#-----------------------------------------------------------------------------# QUERY TUNING #-----------------------------------------------------------------------------# - Planner Method Configuration -

taken

18 #seq_page_cost = 1.0 an arbitrary scale #random_page_cost = 4.0 above #cpu_tuple_cost = 0.01 above #cpu_index_tuple_cost = 0.005 #cpu_operator_cost = 0.0025 #parallel_tuple_cost = 0.1 #parallel_setup_cost = 1000.0

# measured on # same scale as # same scale as # same scale as above # same scale as above # same scale as above # same scale as above

#jit_above_cost = 100000 compilation if available

#

perform

JIT

# and query more expensive than this;

#jit_inline_above_cost = 500000 if query is

# -1 disables # inline small functions

# more expensive than this; 1 disables #jit_optimize_above_cost = 500000 optimizations if

#

use

expensive

JIT

# query is more expensive than this; # -1 disables #min_parallel_table_scan_size = 8MB #min_parallel_index_scan_size = 512kB effective_cache_size = 8192GB # - Genetic Query Optimizer #geqo = on #geqo_threshold = 12 #geqo_effort = 5 #geqo_pool_size = 0 default based on effort #geqo_generations = 0 default based on effort #geqo_selection_bias = 2.0 #geqo_seed = 0.0

# range 1-10 # selects #

selects

# range 1.5-2.0 # range 0.0-1.0

19

# - Other Planner Options #default_statistics_target = 100 #constraint_exclusion = partition #cursor_tuple_fraction = 0.1 #from_collapse_limit = 8 #join_collapse_limit = 8 of explicit

# range 1-10000 # on, off, or partition # range 0.0-1.0 # 1 disables collapsing

# JOIN clauses #force_parallel_mode = off #jit = off JIT compilation

# allow

#-----------------------------------------------------------------------------# REPORTING AND LOGGING #-----------------------------------------------------------------------------# - Where to Log log_destination = 'stderr' combinations of

# Valid values are

# stderr, csvlog, syslog, and eventlog, # depending on platform. csvlog # requires logging_collector to be on. # This is used when logging to stderr: logging_collector = on stderr and csvlog

# Enable capturing of

# into log files. Required to be on for # csvlogs. # (change requires restart) # These are only used if logging_collector is on:

20 #log_directory = 'log' where log files are written,

#

directory

# can be absolute or relative to PGDATA #log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log' #

# log file name pattern, can

include

strftime()

escapes log_file_mode = 0640 mode for log files,

#

creation

# begin with 0 to use octal notation #log_truncate_on_rotation = off file with the

# If on, an existing log

# same name as the new log file will be #

truncated

rather

than

appended to. # But such truncation only occurs on # time-driven rotation, not on restarts # or size-driven rotation. Default is # off, meaning append to existing files # in all cases. #log_rotation_age = 1d rotation of logfiles will

#

Automatic

# happen after that time. 0 disables. #log_rotation_size = 10MB logfiles will

# Automatic rotation of

# happen after that much log output.

21

# 0 disables. # These are relevant when logging to syslog: #syslog_facility = 'LOCAL0' #syslog_ident = 'postgres' #syslog_sequence_numbers = on #syslog_split_messages = on # This is only relevant when logging to eventlog (win32): # (change requires restart) #event_source = 'PostgreSQL' # - When to Log #log_min_messages = warning decreasing detail:

# values in order of

# debug5 # debug4 # debug3 # debug2 # debug1 # info # notice # warning # error # log # fatal # panic #log_min_error_statement = error decreasing detail:

#

values

# debug5

in

order

of

22

# debug4 # debug3 # debug2 # debug1 # info # notice # warning # error # log # fatal # panic (effectively off) #log_min_duration_statement = -1 statements

# -1 is disabled, 0 logs all

# and their durations, > 0 logs only # statements running at least this number # of milliseconds

# - What to Log #debug_print_parse = off #debug_print_rewritten = off #debug_print_plan = off #debug_pretty_print = on #log_checkpoints = off #log_connections = off #log_disconnections = off #log_duration = off

23 #log_error_verbosity = default verbose messages #log_hostname = off #log_line_prefix = '%m [%p] '

# terse, default, or

# special values: # %a = application name # %u = user name # %d = database name # %r = remote host and port # %h = remote host # %p = process ID #

%t = timestamp without

#

%m = timestamp with

#

%n = timestamp with

milliseconds

milliseconds

milliseconds (as a Unix epoch) # %i = command tag # %e = SQL state # %c = session ID # %l = session line number #

%s = session start

timestamp # %v = virtual transaction ID # %x = transaction ID (0 if none) #

%q = stop here in non-

session #

processes

24

# %% = '%'

#log_lock_waits = off >= deadlock_timeout #log_statement = 'none' mod, all #log_replication_commands = off #log_temp_files = -1 temporary files equal or larger

# e.g. ' ' # log lock waits #

none,

ddl,

#

log

# than the specified size in kilobytes; # -1 disables, 0 logs all temp files log_timezone = 'America/Bogota' #-----------------------------------------------------------------------------# PROCESS TITLE #-----------------------------------------------------------------------------#cluster_name = '' process titles if nonempty

#

added

# (change requires restart) #update_process_title = off

#-----------------------------------------------------------------------------# STATISTICS #-----------------------------------------------------------------------------# - Query and Index Statistics Collector #track_activities = on #track_counts = on #track_io_timing = off #track_functions = none #track_activity_query_size = 1024 #stats_temp_directory = 'pg_stat_tmp'

# - Monitoring -

# none, pl, all # (change requires restart)

to

25 #log_parser_stats = off #log_planner_stats = off #log_executor_stats = off #log_statement_stats = off

#-----------------------------------------------------------------------------# AUTOVACUUM #-----------------------------------------------------------------------------#autovacuum = on autovacuum subprocess? 'on'

#

Enable

# requires track_counts to also be on. #log_autovacuum_min_duration = -1 actions and

# -1 disables, 0 logs all

# their durations, > 0 logs only # actions running at least this number

#autovacuum_max_workers = 3 autovacuum subprocesses

#autovacuum_naptime = 1min autovacuum runs #autovacuum_vacuum_threshold = 50 before

#autovacuum_analyze_threshold = 50

# of milliseconds. # max number

of

# (change requires restart) # time between # min number of row updates

# vacuum # min number of row updates

before

#autovacuum_vacuum_scale_factor = 0.2 vacuum #autovacuum_analyze_scale_factor = 0.1 analyze #autovacuum_freeze_max_age = 200000000 forced vacuum

# analyze # fraction of table size before # fraction of table size before # maximum XID age before

26

# (change requires restart) #autovacuum_multixact_freeze_max_age = 400000000 # maximum multixact age # before forced vacuum

#autovacuum_vacuum_cost_delay = 20ms

# (change requires restart) # default vacuum cost delay

for #

autovacuum,

in

milliseconds; # vacuum_cost_delay #autovacuum_vacuum_cost_limit = -1 for

-1

means

use

# default vacuum cost limit

# autovacuum, -1 means use # vacuum_cost_limit

#-----------------------------------------------------------------------------# CLIENT CONNECTION DEFAULTS #-----------------------------------------------------------------------------# - Statement Behavior #client_min_messages = notice decreasing detail:

# values in order of

# debug5 # debug4 # debug3 # debug2 # debug1 # log # notice

27

# warning # error # schema names

#search_path = '"$user", public' #row_security = on #default_tablespace = '' uses the default #temp_tablespaces = '' tablespace names, '' uses

# a tablespace name, '' #

a

list

of

# only default tablespace #check_function_bodies = on #default_transaction_isolation = 'read committed' #default_transaction_read_only = off #default_transaction_deferrable = off #session_replication_role = 'origin' #statement_timeout = 0 milliseconds, 0 is disabled #lock_timeout = 0 milliseconds, 0 is disabled #idle_in_transaction_session_timeout = 0 disabled #vacuum_freeze_min_age = 50000000 #vacuum_freeze_table_age = 150000000 #vacuum_multixact_freeze_min_age = 5000000 #vacuum_multixact_freeze_table_age = 150000000 #vacuum_cleanup_index_scale_factor = 0.1 tuples

#

in

#

in

#

in

milliseconds,

0

is

# fraction of total number of

# before index cleanup, 0 always performs

#bytea_output = 'hex' #xmlbinary = 'base64' #xmloption = 'content' #gin_fuzzy_search_limit = 0 #gin_pending_list_limit = 4MB

# index cleanup # hex, escape

# - Locale and Formatting datestyle = 'iso, dmy' #intervalstyle = 'postgres' timezone = 'America/Bogota' #timezone_abbreviations = 'Default'

# Select the set of available time zone

28

# abbreviations.

Currently,

there are # Default # Australia (historical usage) # India # You can create your own file in

#extra_float_digits = 0

# share/timezonesets/. # min -15, max

3 #client_encoding = sql_ascii database

# actually, defaults to

# encoding # These settings are initialized by initdb, but they can be changed. lc_messages = 'Spanish_Colombia.1252' system error message

#

locale

for

#

locale

for

#

locale

for

# strings lc_monetary = 'Spanish_Colombia.1252' monetary formatting lc_numeric = 'Spanish_Colombia.1252' number formatting lc_time = 'Spanish_Colombia.1252' for time formatting

# locale

# default configuration for text search default_text_search_config = 'pg_catalog.spanish' # - Shared Library Preloading #shared_preload_libraries = '' #local_preload_libraries = '' #session_preload_libraries = '' #jit_provider = 'llvmjit' # - Other Defaults #dynamic_library_path = '$libdir'

# (change requires restart)

# JIT library to use

29

#-----------------------------------------------------------------------------# LOCK MANAGEMENT #-----------------------------------------------------------------------------#deadlock_timeout = 1s #max_locks_per_transaction = 64

# min 10

#max_pred_locks_per_transaction = 64

# (change requires restart) # min 10

#max_pred_locks_per_relation = -2

# (change requires restart) # negative values mean #

(max_pred_locks_per_transaction # max_pred_locks_per_relation) - 1 #max_pred_locks_per_page = 2

/

# min 0

#-----------------------------------------------------------------------------# VERSION AND PLATFORM COMPATIBILITY #-----------------------------------------------------------------------------# - Previous PostgreSQL Versions #array_nulls = on #backslash_quote = safe_encoding #default_with_oids = off #escape_string_warning = on #lo_compat_privileges = off #operator_precedence_warning = off #quote_all_identifiers = off #standard_conforming_strings = on #synchronize_seqscans = on

# on, off, or safe_encoding

# - Other Platforms and Clients #transform_null_equals = off

#-----------------------------------------------------------------------------# ERROR HANDLING

-

30 #-----------------------------------------------------------------------------#exit_on_error = off session on any error? #restart_after_crash = on backend crash? #data_sync_retry = off on failure to fsync

# #

terminate

reinitialize

after

# retry or panic

# data? # (change requires restart)

#-----------------------------------------------------------------------------# CONFIG FILE INCLUDES #-----------------------------------------------------------------------------# These options allow settings to be loaded from files other than the # default postgresql.conf. #include_dir = '' ending in '.conf' from

#include_if_exists = '' only if it exists #include = '' include file

# include files

# a directory, e.g., 'conf.d' # include file

#-----------------------------------------------------------------------------# CUSTOMIZED OPTIONS #-----------------------------------------------------------------------------# Add settings for extensions here

#

31 Capítulo 5 Manejo y Operación del SMBD PostgreSQL

Conexión al SMBD Utilizando la herramienta pgAdmin 4

Conexión al SMBD utilizando SQL Shell

Doble clic al icono SQL Shell (psql)

32

Usuario: postgres Contraseña: especialización Creación base de datos ejemplo ramiro_polindara

33

Verificacion de la creación de la base de datos

Creación de las Bases de Datos pgAdmin (laboratorio)

Clic en Salvar

34

Verificación de la creación de las bases de datos En el navegador del pgAdmin se puede observarr las bases de datos laboratorio y ramiro_polindara

Verificación de la creación de las bases de datos desde sql shell

35 Borrado de la base de datos laboratorio

Verificación del borrado de la base de datos laboratorio

36

Gestión de usuarios Consulta de usuarios creados en el SMBD PostgreSql mediante SQL Shell, comando \du

Consulta de usuarios creados en el SMBD PostgreSql mediante pgAdmin4

37

Creación de usuarios Primero con el nombre ramiro Desde consola CREATE USER ramiro WITH PASSWORD ‘ramiro’; y enter ALTER USER ramiro WITH SUPERUSER; y enter ALTER USER ramiro WITH CREATEDB; y enter ALTER USER ramiro WITH CREATEROLE; y enter ALTER USER ramiro WITH INHERIT LOGIN REPLICATION; y enter

Creación de usuario con el apellido CREATE USER polindara WITH PASSWORD ‘polindara’; y enter ALTER USER polindara WITH INHERIT VALID UNTIL ‘2020-0220T14:29:29-05:00’; y enter

38

Verificación de la creación de los usuarios

39 Creación de otros usuarios

ROL/USUARIO Puede iniciar sesión Puede crear bases de datos Puede crear usuarios Es súper usuario Puede heredar derechos de padres Puede iniciar backups y replicación

admin gerente SI SI SI NO SI SI SI SI SI NO SI NO

supervisor SI NO SI SI SI SI

afiliado SI NO NO NO NO NO

cliente NO NO NO NO NO NO

Verificación de la creación de los usuarios

Modificación del usuario cliente para que posea contraseña, la cuenta la pueda utilizar por dos meses y pueda heredar derechos de los usuarios padres ALTER USER cliente PASSWORD ‘cliente’; y enter ALTER USER cliente WITH INHERIT VALID UNTIL ‘2019-10-20T16:11:4505:00’; y enter ALTER USER cliente WITH NOLOGIN; y enter ALTER USER cliente WITH NOSUPERUSER; y enter ALTER USER cliente WITH NOCREATEDB; y enter

40 ALTER USER cliente WITH NOCREATEROLE; y enter ALTER USER cliente WITH INHERIT NOREPLICATION; y enter

Verificación del cambio al usuario cliente

Modificación del usuario gerente para quitarle privilegios de superusuario, que pueda heredar derechos de los usuarios padres e iniciar backups. ALTER USER gerente WITH NOSUPERUSER; y enter ALTER USER gerente WITH INHERIT REPLICATION; y enter

41

Verificación de cambios al usuario gerente

Modificacion usuario supervisor, para quitarle el rol de superusuario y el de replicación. ALTER USER supervisor WITH NOSUPERUSER; y enter ALTER USER supervisor WITH INHERIT REPLICATION; y enter

42

Borrado usuario cliente DROP USER cliente; y enter Verificación borrado usuario cliente

43 Administración de Servicios

Servicios que postgresql esta ejecutando

Msconfig Servicios,

44

Se puede verificar que el servicio postgresql-x64-11 esta activo y se carga automáticamente al iniciar el sistema operativo.

El servicio se puede detener y reiniciar así:

45

Administración del SMBD desde SQL Shell Creación Tablespace desde el SQL Shell Creación tablespace Sena

Quedo ubicado en C:\DataPostgreSQL\Sena\PG_11_201809051 Creación base de datos sena

46

Verificación de la creación de la base de datos sena

Conectarse a la base de datos \c sena y enter

Creación del schema especialización

47

Creación de tablas de la base de datos

Con \e se ejecutan las instrucciones sql desde un archivo editado en el bloq de notas

48

El archivo contiene las instrucciones de la tabla a crear

CREATE TABLE especializacion.institucion(CodIns serial PRIMARY KEY, NomIns varchar(30) NOT NULL, DirIns varchar(30) NOT NULL, TelIns varchar(15) NOT NULL);

Posteriormente se le da guardar y salir, pasando al editor SQL Shell donde se ejecutan las instrucciones.

49

Creación de otras tablas

Verificación de la creación de las tablas en pgAdmin