Inicializndo Postgresql desde linea de comando
Publicado por SuprIndioSuperIndio (4 intervenciones) el 04/05/2024 00:00:46
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
initdb — create a new PostgreSQL database cluster
SET PGBIN=C:\Development\pg
SET PGDATA=C:\Development\datapg\
SET LOGDIR=C:\Development\datalog\Trapterlog.log
%PGBIN%\initdb -A -D "C:\Development\datapg\"
or
%PGBIN%\initdb -A -D %PGDATA%
pg_archivecleanup — clean up PostgreSQL WAL archive files
pg_checksums — enable, disable or check data checksums in a PostgreSQL database clusterpg_controldata — display control information of a PostgreSQL database cluster
pg_ctl — initialize, start, stop, or control a PostgreSQL server
%PGBIN%\pg_ctl start -D "C:\Development\datapg\" -l %LOGDIR% start
or
%PGBIN%\pg_ctl status -D %PGDATA%
pg_resetwal — reset the write-ahead log and other control information of a PostgreSQL database clusterpg_rewind — synchronize a PostgreSQL data directory with another data directory that was forked from it
pg_test_fsync — determine fastest wal_sync_method for PostgreSQL
pg_test_timing — measure timing overhead
pg_upgrade — upgrade a PostgreSQL server instance
pg_waldump — display a human-readable rendering of the write-ahead log of a PostgreSQL database cluster
postgres — PostgreSQL database server
mkdir C:\MyAwesomeDB
C:\PostgreSQL\13\bin\initdb -U MyAwesomeUserID -W -D C:\MyAwesomeDB
C:\PostgreSQL\13\bin\pg_ctl.exe -D C:\MyAwesomeDB -l logfile start
C:\PostgreSQL\13\bin\pg_ctl.exe stop -D C:\MyAwesomeDB
service
service postgresql-10 startservice postgresql-10 stopservice postgresql-10 statuspg_ctl
pg_ctl
pg_ctl start -D [data dir]
pg_ctl stop -D [data dir]
pg_ctl status -D [data dir]
psql -U elvis -h localhost -D c:\PGSql\Datapg\
\l listar bases de datos de un seridor postgresqlSELECT * FROM pg_database WHERE datname = 'dbGalamin';
\connect dbGalamin\dt Listar Tablas de una BASESELECT table_name --seleccionamos solo la columna del nombre de la tabla
FROM information_schema.tables --seleccionamos la información del esquema
WHERE table_schema='public' --las tablas se encuentran en el esquema publico
AND table_type='BASE TABLE'; --tiene que ser del tipo table ya que aqui se listan tamb
SELECT table_name FROM information_schema.tables WHERE table_schema='public' AND table_type='BASE TABLE';
SELECT * FROM pg_catalog.pg_tables;
dbGalamin=# SELECT * FROM pg_catalog.pg_tables;
schemaname | tablename | tableowner | tablespace | hasindexes | hasrules | hastriggers | rowsecurity
--------------------+-------------------------+------------+------------+------------+----------+-------------+-------------
pg_catalog | pg_statistic | elvis | | t | f | f | f
pg_catalog | pg_type | elvis | | t | f | f | f
pg_catalog | pg_policy | elvis | | t | f | f | f
pg_catalog | pg_authid | elvis | pg_global | t | f | f | f
pg_catalog | pg_user_mapping | elvis | | t | f | f | f
pg_catalog | pg_subscription | elvis | pg_global | t | f | f | f
pg_catalog | pg_attribute | elvis | | t | f | f | f
pg_catalog | pg_proc | elvis | | t | f | f | f
pg_catalog | pg_class | elvis | | t | f | f | f
pg_catalog | pg_attrdef | elvis | | t | f | f | f
pg_catalog | pg_constraint | elvis | | t | f | f | f
pg_catalog | pg_inherits | elvis | | t | f | f | f
pg_catalog | pg_index | elvis | | t | f | f | f
pg_catalog | pg_operator | elvis | | t | f | f | f
pg_catalog | pg_opfamily | elvis | | t | f | f | f
pg_catalog | pg_opclass | elvis | | t | f | f | f
pg_catalog | pg_am | elvis | | t | f | f | f
pg_catalog | pg_amop | elvis | | t | f | f | f
pg_catalog | pg_amproc | elvis | | t | f | f | f
pg_catalog | pg_language | elvis | | t | f | f | f
pg_catalog | pg_largeobject_metadata | elvis | | t | f | f | f
pg_catalog | pg_aggregate | elvis | | t | f | f | f
pg_catalog | pg_statistic_ext | elvis | | t | f | f | f
pg_catalog | pg_rewrite | elvis | | t | f | f | f
pg_catalog | pg_trigger | elvis | | t | f | f | f
pg_catalog | pg_event_trigger | elvis | | t | f | f | f
pg_catalog | pg_description | elvis | | t | f | f | f
pg_catalog | pg_cast | elvis | | t | f | f | f
pg_catalog | pg_enum | elvis | | t | f | f | f
pg_catalog | pg_namespace | elvis | | t | f | f | f
pg_catalog | pg_conversion | elvis | | t | f | f | f
pg_catalog | pg_depend | elvis | | t | f | f | f
pg_catalog | pg_database | elvis | pg_global | t | f | f | f
pg_catalog | pg_db_role_setting | elvis | pg_global | t | f | f | f
pg_catalog | pg_tablespace | elvis | pg_global | t | f | f | f
pg_catalog | pg_pltemplate | elvis | pg_global | t | f | f | f
pg_catalog | pg_auth_members | elvis | pg_global | t | f | f | f
pg_catalog | pg_shdepend | elvis | pg_global | t | f | f | f
pg_catalog | pg_shdescription | elvis | pg_global | t | f | f | f
pg_catalog | pg_ts_config | elvis | | t | f | f | f
pg_catalog | pg_ts_config_map | elvis | | t | f | f | f
pg_catalog | pg_ts_dict | elvis | | t | f | f | f
pg_catalog | pg_ts_parser | elvis | | t | f | f | f
pg_catalog | pg_ts_template | elvis | | t | f | f | f
pg_catalog | pg_extension | elvis | | t | f | f | f
pg_catalog | pg_foreign_data_wrapper | elvis | | t | f | f | f
pg_catalog | pg_foreign_server | elvis | | t | f | f | f
pg_catalog | pg_foreign_table | elvis | | t | f | f | f
pg_catalog | pg_replication_origin | elvis | pg_global | t | f | f | f
pg_catalog | pg_default_acl | elvis | | t | f | f | f
pg_catalog | pg_init_privs | elvis | | t | f | f | f
pg_catalog | pg_seclabel | elvis | | t | f | f | f
pg_catalog | pg_shseclabel | elvis | pg_global | t | f | f | f
pg_catalog | pg_collation | elvis | | t | f | f | f
pg_catalog | pg_partitioned_table | elvis | | t | f | f | f
pg_catalog | pg_range | elvis | | t | f | f | f
pg_catalog | pg_transform | elvis | | t | f | f | f
pg_catalog | pg_sequence | elvis | | t | f | f | f
pg_catalog | pg_publication | elvis | | t | f | f | f
pg_catalog | pg_publication_rel | elvis | | t | f | f | f
pg_catalog | pg_subscription_rel | elvis | | t | f | f | f
pg_catalog | pg_largeobject | elvis | | t | f | f | f
information_schema | sql_parts | elvis | | f | f | f | f
information_schema | sql_languages | elvis | | f | f | f | f
information_schema | sql_features | elvis | | f | f | f | f
information_schema | sql_implementation_info | elvis | | f | f | f | f
information_schema | sql_packages | elvis | | f | f | f | f
information_schema | sql_sizing | elvis | | f | f | f | f
information_schema | sql_sizing_profiles | elvis | | f | f | f | f
(69 filas)
dbGalamin=#
-- Set PATH=%PATH%;c:\Build\pgsql\bin;c:\Build\pgsql\lib
-- c:\Build\pgsql\bin\psql -h localhost -U elvis -W -f dbEscuela8.sql ELVISdb
--
-- psql BaseDeDatos < /ruta/archivo.sql
--
--
-- $ sudo bash
-- # su postgres
-- $ psql -U postgres -d base_de_datos -f archivo.sql
--
-- – U para el usuario que maneja postgres
--
-- – d Nombre de la base de datos
--
-- -f Archivo sql a importar
--
--
-- psql Prueba -h localhost -U postgres < creacion.sql
--
--
-- pg_dump Prueba -h localhost -U postgres > script.sql
--
CREATE USER ELVIS WITH PASSWORD 'ELVIS';CREATE USER ELVISEL WITH PASSWORD 'ELVISEL';CREATE USER SYSDBA WITH PASSWORD 'SYSDBA';CREATE USER SYSADMIN WITH PASSWORD 'SYSADMIN';dbGalamin=#
dbGalamin=# CREATE USER ELVIS WITH PASSWORD 'ELVIS';
CREATE ROLE
dbGalamin=# CREATE USER ELVISEL WITH PASSWORD 'ELVISEL';
CREATE ROLE
dbGalamin=# CREATE USER SYSDBA WITH PASSWORD 'SYSDBA';
CREATE ROLE
dbGalamin=# CREATE USER SYSADMIN WITH PASSWORD 'SYSADMIN';
CREATE ROLE
dbGalamin=# COMMIT;
WARNING: no hay una transacción en cursoCOMMIT
dbGalamin=# \du
Lista de roles
Nombre de rol | Atributos | Miembro de
---------------+------------------------------------------------------------+------------
elvis | Superusuario, Crear rol, Crear BD, Replicaci=n, Ignora RLS | {}
admin | | {}
atlas | | {}
ELVIS | | {}
postgres | | {}
root | | {}
sysadmin | | {}
sysdba | | {}
PASAR DE USER TO SUPERUSER
**********************************
ALTER USER ELVIS WITH SUPERUSER;
ALTER USER ELVISEL WITH SUPERUSER;
ALTER USER SYSDBA WITH SUPERUSER;
ALTER USER SYSADMIN WITH SUPERUSER;
dbGalamin=# ALTER USER ELVIS WITH SUPERUSER;
ALTER ROLE
dbGalamin=# ALTER USER ELVISEL WITH SUPERUSER;
ALTER ROLE
dbGalamin=# ALTER USER SYSDBA WITH SUPERUSER;
ALTER ROLE
dbGalamin=# ALTER USER SYSADMIN WITH SUPERUSER;
ALTER ROLE
dbGalamin=# \du
Lista de roles
Nombre de rol | Atributos | Miembro de
---------------+------------------------------------------------------------+------------
elvis | Superusuario, Crear rol, Crear BD, Replicaci=n, Ignora RLS | {}
admin | | {}
atlas | | {}
ELVIS | Superusuario | {}
postgres | | {}
root | | {}
sysadmin | Superusuario | {}
sysdba | Superusuario | {}
Valora esta pregunta
0