Skip to main content

PostgreSQL Daliy | PosgteSQL adninistration

List databases:

postgres-# \l
                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 test      | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
(4 rows)

Connect to database:
postgres-# \connect postgres
You are now connected to database "postgres" as user "postgres".

List schema:
postgres-# \dn[S+]
 information_schema | postgres | postgres=UC/postgres+|
                    |          | =U/postgres          |
 pg_catalog         | postgres | postgres=UC/postgres+| system catalog schema
                    |          | =U/postgres          |
 pg_temp_1          | postgres |                      |
 pg_toast           | postgres |                      | reserved schema for TOAST tables
 pg_toast_temp_1    | postgres |                      |
 public             | postgres | postgres=UC/postgres+| standard public schema
                    |          | =UC/postgres         |
Roles:
postgres-#  \dg[S+]
 pg_execute_server_program | Cannot login                                               | {}                                                           |
 pg_monitor                | Cannot login                                               | {pg_read_all_settings,pg_read_all_stats,pg_stat_scan_tables} |
 pg_read_all_settings      | Cannot login                                               | {}                                                           |
 pg_read_all_stats         | Cannot login                                               | {}                                                           |
 pg_read_server_files      | Cannot login                                               | {}                                                           |
 pg_signal_backend         | Cannot login                                               | {}                                                           |
 pg_stat_scan_tables       | Cannot login                                               | {}                                                           |
 pg_write_server_files     | Cannot login                                               | {}                                                           |
 postgres                  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}   
 
postgres-# \connect postgres
You are now connected to database "postgres" as user "postgres".

List tables of the database postgres:
postgres-# \dt[S+]
 pg_catalog | pg_aggregate            | table | postgres | 56 kB      |
 pg_catalog | pg_am                   | table | postgres | 40 kB      |
 pg_catalog | pg_amop                 | table | postgres | 80 kB      |
 pg_catalog | pg_amproc               | table | postgres | 56 kB      |
 pg_catalog | pg_attrdef              | table | postgres | 8192 bytes |
 pg_catalog | pg_attribute            | table | postgres | 464 kB     |
 pg_catalog | pg_auth_members         | table | postgres | 40 kB      |
 pg_catalog | pg_authid               | table | postgres | 48 kB      |
 pg_catalog | pg_cast                 | table | postgres | 48 kB      |
 pg_catalog | pg_class                | table | postgres | 136 kB     |
 pg_catalog | pg_collation            | table | postgres | 424 kB     |
 pg_catalog | pg_constraint           | table | postgres | 48 kB      |
 pg_catalog | pg_conversion           | table | postgres | 56 kB      |
 pg_catalog | pg_database             | table | postgres | 48 kB      |
 pg_catalog | pg_db_role_setting      | table | postgres | 8192 bytes |
 pg_catalog | pg_default_acl          | table | postgres | 8192 bytes |
 pg_catalog | pg_depend               | table | postgres | 480 kB     |
 pg_catalog | pg_description          | table | postgres | 360 kB     |
 pg_catalog | pg_enum                 | table | postgres | 0 bytes    |
 pg_catalog | pg_event_trigger        | table | postgres | 8192 bytes |
 pg_catalog | pg_extension            | table | postgres | 48 kB      |
 pg_catalog | pg_foreign_data_wrapper | table | postgres | 8192 bytes |
 pg_catalog | pg_foreign_server       | table | postgres | 8192 bytes |
 pg_catalog | pg_foreign_table        | table | postgres | 8192 bytes |
 pg_catalog | pg_index                | table | postgres | 64 kB      |
 pg_catalog | pg_inherits             | table | postgres | 0 bytes    |
 pg_catalog | pg_init_privs           | table | postgres | 56 kB      |
 pg_catalog | pg_language             | table | postgres | 48 kB      |
 pg_catalog | pg_largeobject          | table | postgres | 0 bytes    |
 pg_catalog | pg_largeobject_metadata | table | postgres | 0 bytes    |
 pg_catalog | pg_namespace            | table | postgres | 48 kB      |
 pg_catalog | pg_opclass              | table | postgres | 48 kB      |
 pg_catalog | pg_operator             | table | postgres | 144 kB     |
 pg_catalog | pg_opfamily             | table | postgres | 48 kB      |
 pg_catalog | pg_partitioned_table    | table | postgres | 8192 bytes |
 pg_catalog | pg_pltemplate           | table | postgres | 48 kB      |
 pg_catalog | pg_policy               | table | postgres | 8192 bytes |
 pg_catalog | pg_proc                 | table | postgres | 672 kB     |
 pg_catalog | pg_publication          | table | postgres | 0 bytes    |
 pg_catalog | pg_publication_rel      | table | postgres | 0 bytes    |
 pg_catalog | pg_range                | table | postgres | 40 kB      |
 pg_catalog | pg_replication_origin   | table | postgres | 8192 bytes |
 pg_catalog | pg_rewrite              | table | postgres | 632 kB     |
 pg_catalog | pg_seclabel             | table | postgres | 8192 bytes |
 pg_catalog | pg_sequence             | table | postgres | 0 bytes    |
 pg_catalog | pg_shdepend             | table | postgres | 40 kB      |
 pg_catalog | pg_shdescription        | table | postgres | 48 kB      |
 pg_catalog | pg_shseclabel           | table | postgres | 8192 bytes |
 pg_catalog | pg_statistic            | table | postgres | 256 kB     |
 pg_catalog | pg_statistic_ext        | table | postgres | 8192 bytes |
 pg_catalog | pg_statistic_ext_data   | table | postgres | 8192 bytes |
 pg_catalog | pg_subscription         | table | postgres | 8192 bytes |
 pg_catalog | pg_subscription_rel     | table | postgres | 0 bytes    |
 pg_catalog | pg_tablespace           | table | postgres | 48 kB      |
 pg_catalog | pg_transform            | table | postgres | 0 bytes    |
 pg_catalog | pg_trigger              | table | postgres | 8192 bytes |
 pg_catalog | pg_ts_config            | table | postgres | 40 kB      |
 pg_catalog | pg_ts_config_map        | table | postgres | 56 kB      |
 pg_catalog | pg_ts_dict              | table | postgres | 48 kB      |
 pg_catalog | pg_ts_parser            | table | postgres | 40 kB      |
 pg_catalog | pg_ts_template          | table | postgres | 40 kB      |
 pg_catalog | pg_type                 | table | postgres | 120 kB     |
 pg_catalog | pg_user_mapping         | table | postgres | 8192 bytes |


Labels

Labels:
Location: San Francisco, CA, USA

Comments

Post a Comment

Popular posts from this blog

MySQL InnoDB cluster troubleshooting | commands

Cluster Validation: select * from performance_schema.replication_group_members; All members should be online. select instance_name, mysql_server_uuid, addresses from  mysql_innodb_cluster_metadata.instances; All instances should return same value for mysql_server_uuid SELECT @@GTID_EXECUTED; All nodes should return same value Frequently use commands: mysql> SET SQL_LOG_BIN = 0;  mysql> stop group_replication; mysql> set global super_read_only=0; mysql> drop database mysql_innodb_cluster_metadata; mysql> RESET MASTER; mysql> RESET SLAVE ALL; JS > var cluster = dba.getCluster() JS > var cluster = dba.getCluster("<Cluster_name>") JS > var cluster = dba.createCluster('name') JS > cluster.removeInstance('root@<IP_Address>:<Port_No>',{force: true}) JS > cluster.addInstance('root@<IP add>,:<port>') JS > cluster.addInstance('root@ <IP add>,:<port> ') JS > dba.getC...
Post a Comment
Read more

InnoDB cluster Remove Instance Force | Add InnoDB instance

InnoDB cluster environment UUID is different on node: To fix it stop group replication, remove instance (use force if require), add instance back Identify the node which is not in sync: Execute following SQL statement on each node and identify the node has different UUID on all nodes. mysql> select * from mysql_innodb_cluster_metadata.instances; Stop group replication: Stop group replication on the node which does not have same UUID on all nodes. mysql > stop GROUP_REPLICATION; Remove instances from cluster: Remove all secondary node from the cluster and add them back if require. $mysqlsh JS >\c root@<IP_Address>:<Port_No> JS > dba.getCluster().status() JS > dba.getCluster () <Cluster:cluster_name> JS > var cluster = dba.getCluster("cluster_name"); JS >  cluster.removeInstance('root@<IP_Address>:<Port_No>'); If you get "Cluster.removeInstance: Timeout reached waiting......" JS > cluster.removeInstance(...
2 comments
Read more

Oracle E-Business Suite Online Patch Phases executing adop

Following description about Oracle E-Business Suite is high level and from documentation https://docs.oracle.com/cd/E26401_01/doc.122/e22954/T202991T531062.htm#5281339 for in depth and detail description refer it. The online patching cycle phases: Prepare Apply Finalize Cutover Cleanup Prepare phase: Start a new online patching cycle, Prepares the environment for patching. $ adop phase=prepare Apply phase: Applies the specified patches to the environment. Apply one or more patches to the patch edition. $ adop phase=apply patches=123456,789101 workers=8 Finalize phase: Performs any final steps required to make the system ready for cutover. Perform the final patching operations that can be executed while the application is still online. $ adop phase=finalize Cutover phase: Shuts down application tier services, makes the patch edition the new run edition, and then restarts application tier services. This is the only phase that involves a brief ...
Post a Comment
Read more