Maclean’s Oracle Database Tech Blog Archives

but could not open file pg_control: No such file or directory postgres@vultr:~/10/main/global$ pg_ctl start -D $PGDATA waiting for server to start….postgres: could not find the database system Expected to find it in the directory “/var/lib/postgresql/10/main”, but could not open file “/var/lib/postgresql/10/main/global/pg_control”: No such file or directory stopped waiting pg_ctl: could not start server Examine the…

文章来源：https://billtian.github.io/digoal.blog/2016/08/14/02.html   背景 PostgreSQL 控制文件在$PGDATA/global目录下名为pg_control. 控制文件中记录了以下三部分信息 :     1. initdb时生成的静态信息 : pg_control version number: 922 Catalog version number: 201204301 Database system identifier: 5831753892046499175 Maximum data alignment: 8 Database block size: 8192 Blocks per segment of large relation: 131072 WAL block size: 16384 Bytes per WAL segment: 16777216 Maximum length of identifiers: 64 Maximum columns…

postgreSQL 如果没有正常关闭数据库且丢失了xlog 或者说wal日志文件的话 会导致启动时出现上述错误，对于pg而言可以通过pg_resetxlog或pg_resetwal(pg 10以后)来绕过该问题，即跳过这些日志。不像在oracle中，如果是没有clean close database且丢失了acitve或current的重做日志的话，会导致oracle数据库也无法打开，且强制开库的话步骤十分复杂，要比pg困难很多。   PG中可以执行如下命令来重置日志，但是注意做这些操作之前应该对$PGDATA目录做一个tarbar备份：     2019-11-19 06:37:09.459 UTC [2705] FATAL: terminating autovacuum process due to administrator command 2019-11-19 06:38:35.142 UTC [2791] LOG: listening on IPv6 address “::1”, port 5432 2019-11-19 06:38:35.142 UTC [2791] LOG: listening on IPv4 address “127.0.0.1”, port 5432 2019-11-19 06:38:35.143 UTC [2791] LOG: listening on Unix…

postgreSQL中一般 一个表或索引会对应一个或多个数据文件，可以通过下面的方法获得表对应的数据文件位置：     postgres=# create table testtab (t1 int); CREATE TABLE postgres=# postgres=# SELECT pg_relation_filepath(‘testtab’); pg_relation_filepath ———————- base/13055/16384 (1 row)

  https://zcdn.askmac.cn/recover.bsq

— this file generated mechanically from dbmsbkrs.pls create or replace PACKAGE dbms_backup_restore IS ————- — Version — ————- — Advance the min numbers whenever support is removed for old — versions of the RMAN client. Advance the max number whenever — new functions are added or existing ones are changed (once per release — cycle).…

package dbms_backup_restore PROCEDURE backupBackupPiece( bpname IN varchar2 ,fname IN varchar2 ,handle OUT varchar2 ,comment OUT varchar2 ,media OUT varchar2 ,concur OUT boolean ,recid OUT number ,stamp OUT number ,tag IN varchar2 default NULL ,params IN varchar2 default NULL ,media_pool IN binary_integer default 0 ,reuse IN boolean default FALSE); — This procedure copies a backup piece…

文章来源：https://www.jianshu.com/p/cd8c5b988e52 PostgreSQL数据目录结构   根目录介绍   data ├── base # use to store database file(SELECT oid, datname FROM pg_database;) ├── global # under global, all the filenode is hard-code(select oid,relname,relfilenode from pg_class where relfilenode=0 order by oid;) ├── pg_clog # dir of transaction commit log │ └── 0000 ├── pg_commit_ts ├── pg_dynshmem ├── pg_hba.conf # client…

文章来源： http://blog.sina.com.cn/s/blog_74a7d3390102yksv.html PostgreSQL控制文件内容： 主要分为是三部分，初始化静态信息、WAL及检查点的动态信息、一些配置信息。 我们可以用过pg_controldata命令直接读取PostgreSQL控制文件内容：   [postgres@postgresdb ~]$ /u01/postgres/pgsql/bin/pg_controldata -D /data/postgres/data pg_control version number: 1100 Catalog version number: 201809051 Database system identifier: 6709564017377676696 Database cluster state: in production pg_control last modified: Wed 17 Jul 2019 02:27:12 PM HKT Latest checkpoint location: 5A/F522A8E0 Latest checkpoint’s REDO location: 5A/F522A8A8 Latest checkpoint’s REDO WAL file: 000000010000005A000000F5 Latest checkpoint’s…