Search for a command to run...
As a passionate DevOps Engineer with 3+ years of experience, I specialize in building robust, scalable, and secure infrastructures. My expertise spans Kubernetes, Jenkins, Docker, AWS, Ansible, Flask, Apache, Nginx, Kibana, Uyuni, Percona PMM, MySQL, and more.
No comments yet. Be the first to comment.
📋 Prerequisites Install base tools and keys: sudo apt update && sudo apt install -y \ curl gnupg2 ca-certificates lsb-release debian-archive-keyring Add NGINX.org signing key: curl https://nginx.org/keys/nginx_signing.key | gpg --dearmor | sudo t...
I know we have been in a situation where the PHP application goes down even if there is no CPU and memory usage on both the application and database on the server. This document explains the common bottleneck and blockage issues that occur when using...
This article explains how to install and manage Uyuni on openSUSE Leap Micro 5.5 and above. The purpose of setting up Uyuni is primarily for patch management, configuration management, and integrating Ansible control. Uyuni serves as a centralized ma...
In this guide, we'll learn how to export Kibana detection rules using the Kibana Saved Objects API and store them in an S3 bucket. This approach ensures you have backups of your Kibana rules and can restore them whenever needed. Why Do You Need This?...
In today's world, it's crucial to monitor your application logs and get alerted about suspicious or critical activities. If you are using Kibana for log monitoring and want to get real-time alerts on Microsoft Teams or Slack, this guide is for you. T...
login to postgresql user
su - postgres
To enter in postgresql:-
su - postgres
psql
To create database
create database <DatabaseName>;
Create user with password
create user <username> with encrypted password 'password';
To give permission to user with database.
grant all privileges on database <databasename> to <username>;
To give superuser permission
ALTER USER username WITH SUPERUSER;
To login remotely from others place
psql -U username -h localhost -p 5432 dbname
To dump from remote server
pg_dump -U <username> -h <host-address> -p 5432 <databasename> > databasename.sql
To dump postgres database
pg_dump databasename > dbname.sql
To restore db dump
psql databasename < path-dbname.sql
database dump of single table
pg_dump -d <database_name> -t <table_name> > file.sql
and to restore single table
psql -h localhost -U postgres -p 5432 database < path/to/store/name.sql
Postgres schema only dump
pg_dump -U postgres -s postgres > exportFile.sql
Postgres schema only with specific table
pg_dump -s -t tablename databasename > dump.sql
Login directly to postgres
sudo -u postgres psql
To drop database with force
DROP DATABASE <database-name> WITH (FORCE);
To list database
\c dbname | Switch connection to a new database |
|
\l | List available databases |
|
\dt | List available tables |
|
\d table_name | Describe a table such as a column, type, modifiers of columns, etc. |
|
\dn | List all schemes of the currently connected database |
|
\df | List available functions in the current database |
|
\dv | List available views in the current database |
|
\du | List all users and their assign roles |
|
SELECT version(); | Retrieve the current version of PostgreSQL server |
|
\g | Execute the last command again |
|
\s | Display command history |
|
\s filename | Save the command history to a file |
|
\i filename | Execute psql commands from a file |
|
\? | Know all available psql commands |
|
\h | Get help | Eg:to get detailed information on ALTER TABLE statement use the \h ALTER TABLE |
\e | Edit command in your own editor |
|
\a | Switch from aligned to non-aligned column output |
|
\H | Switch the output to HTML format |
|
\q | Exit psql shell |
|
Creating Extensions
CREATE EXTENSION <extension_name>;
Make sure you switch to specified databases and enable extension and install necessary packages before enabling
You can also check below options in Postgres
Usage:
pg_dump [OPTION]... [DBNAME]
General options:
-f, --file=FILENAME output file or directory name
-F, --format=c|d|t|p output file format (custom, directory, tar,
plain text (default))
-j, --jobs=NUM use this many parallel jobs to dump
-v, --verbose verbose mode
-V, --version output version information, then exit
-Z, --compress=0-9 compression level for compressed formats
--lock-wait-timeout=TIMEOUT fail after waiting TIMEOUT for a table lock
-?, --help show this help, then exit
Options controlling the output content:
-a, --data-only dump only the data, not the schema
-b, --blobs include large objects in dump
-c, --clean clean (drop) database objects before recreating
-C, --create include commands to create database in dump
-E, --encoding=ENCODING dump the data in encoding ENCODING
-n, --schema=SCHEMA dump the named schema(s) only
-N, --exclude-schema=SCHEMA do NOT dump the named schema(s)
-o, --oids include OIDs in dump
-O, --no-owner skip restoration of object ownership in
plain-text format
-s, --schema-only dump only the schema, no data
-S, --superuser=NAME superuser user name to use in plain-text format
-t, --table=TABLE dump the named table(s) only
-T, --exclude-table=TABLE do NOT dump the named table(s)
-x, --no-privileges do not dump privileges (grant/revoke)
--binary-upgrade for use by upgrade utilities only
--column-inserts dump data as INSERT commands with column names
--disable-dollar-quoting disable dollar quoting, use SQL standard quoting
--disable-triggers disable triggers during data-only restore
--enable-row-security enable row security (dump only content user has
access to)
--exclude-table-data=TABLE do NOT dump data for the named table(s)
--if-exists use IF EXISTS when dropping objects
--inserts dump data as INSERT commands, rather than COPY
--no-security-labels do not dump security label assignments
--no-synchronized-snapshots do not use synchronized snapshots in parallel jobs
--no-tablespaces do not dump tablespace assignments
--no-unlogged-table-data do not dump unlogged table data
--quote-all-identifiers quote all identifiers, even if not key words
--section=SECTION dump named section (pre-data, data, or post-data)
--serializable-deferrable wait until the dump can run without anomalies
--snapshot=SNAPSHOT use given snapshot for the dump
--strict-names require table and/or schema include patterns to
match at least one entity each
--use-set-session-authorization
use SET SESSION AUTHORIZATION commands instead of
ALTER OWNER commands to set ownership
Connection options:
-d, --dbname=DBNAME database to dump
-h, --host=HOSTNAME database server host or socket directory
-p, --port=PORT database server port number
-U, --username=NAME connect as specified database user
-w, --no-password never prompt for password
-W, --password force password prompt (should happen automatically)
--role=ROLENAME do SET ROLE before dump
If no database name is supplied, then the PGDATABASE environment
variable value is used.