2025-06-11T18:05:46Z

Chubbard: /* Slim4 Framework Notes */

===PHP Notes and Examples===
==change Array to JSON ==
<pre>
$foo=json_encode($array,1);
echo $foo . "\n";
</pre>

==Convert a VALID JSON string to array==
<pre>
$foo=json_decode($json, True);
print_r($foo);
</pre>

==Example foreach loop (simple)==
<pre>
foreach ($nestedArray1 as $Array1) {
do something with new array $array1[??};
}
</pre>

==Example Try Catch block==
<pre>
Use Exception;
try {
someRandoFunction;
}
catch (Exception $e) {
doSomethingWhenErrorFound;
}
catch (ErrorName $e) {
cryToMamma;
}
</pre>

==Checking if file or dir exists. Starting point:==
<pre>

<?php
$path_parts = pathinfo('/www/htdocs/inc/lib.inc.php');

echo $path_parts['dirname'], "\n";
echo $path_parts['basename'], "\n";
echo $path_parts['extension'], "\n";
echo $path_parts['filename'], "\n";
?>

The above example will output:

/www/htdocs/inc
lib.inc.php
php
lib.inc
</pre>

==Go BACKWARDS in array==
From: [https://stackoverflow.com/questions/2194388/php-how-to-get-the-element-before-the-last-element-from-an-array php-how-to-get-the-element-before-the-last-element-from-an-array]
* Note that you need more than 2 elements in the array for this to work properly according to answer.
<pre>
<?php
$foo=["one","two","three","four"];
echo "TEST " . $foo[count($foo)-2];
?>
Returns TEST three
</pre>

All arrays have an "internal array pointer" which points to the current array element, PHP has several functions which allow you to navigate through the array and view the current elements key and value.

end() - Set the internal pointer of an array to its last element
reset() - Set the internal pointer of an array to its first element
prev() - Rewind the internal array pointer
next() - Advance the internal array pointer of an array
current() - Return the current element in an array
key() - Fetch a key from an array
each() - Return the current key and value pair from an array and advance the array cursor

== Add Space before every capital letter==
From [https://bytes.com/topic/php/answers/8589-how-insert-space-before-capital-letters-string -how-insert-space-before-capital-letters-string]
<pre>
preg_replace('/(\w+)([A-Z])/U', '\\1 \\2', $cleanStorage);
</pre>

== Working with file creation and removal ==
<pre>
$content = '<?php ' . $preProcessing . ' ?>';
$file = dirname(__FILE__) . '/'. $event_name . '.php';
file_put_contents($file, $content);
include "$file";
unlink "$file";
$result['file'] = $file;
</pre>

== Set defaults if not provided to function ==
[https://stackoverflow.com/questions/9166914/using-default-arguments-in-a-function| Using defaults in a function in PHP]
<pre>
function foo($blah, $x = null, $y = null) {
if (null === $x) {
$x = "some value";
}
if (null === $y) {
$y = "some other value";
}
code here!
}
</pre>

== Make Indexed Array Unique ==
<pre>
$temp_array = array();
foreach ($array as &$v) {
if (!isset($temp_array[$v['name']]))
$temp_array[$v['name']] =& $v;
}
</pre>

== Array by reference ==
Using the & VARIABLE will change the value within the original array. This was given by chatGPT questions. Nice simple example
<pre>
// Without reference
$array1 = [1, 2, 3];
foreach ($array1 as $v) {
$v = $v * 2;
}
print_r($array1); // Outputs: Array ( [0] => 1 [1] => 2 [2] => 3 )

// With reference
$array2 = [1, 2, 3];
foreach ($array2 as &$v) {
$v = $v * 2;
}
print_r($array2); // Outputs: Array ( [0] => 2 [1] => 4 [2] => 6 )
</pre>

==Slim4 Framework Notes==
<pre>
return $this->respondWithData($data);
throw new HttpBadRequestException($this->request, "Error message details");
</pre>

==Simple PHP validation==
<pre>
find src -type f -name '*.php' -exec php -l {} \;
</pre>

== Some cheat-sheets ==
Random cheat-sheets found on the net that are kinda useful

# [https://cheatography.com/davechild/cheat-sheets/php/| Looks like a pretty good PHP sheet]
# [https://cheatography.com/krabat1/cheat-sheets/php/| This one is REALLY big, but looks to have good info]
[[Category:PHP]]

Git notes

2025-05-27T16:18:29Z

Chubbard: /* mis-synced between parent and submodule commits */

Git notes

2025-05-16T15:24:40Z

Chubbard: /* Drop changes and pull submodules */

Git notes

2025-05-16T15:23:39Z

Chubbard: /* Nuke a bad commit before push */

=== General git commands that are useful ===
== Change git remote:==

With or without ssh keys added to your user account..

<pre>
git remote set-url origin git@gitlab01.iwillfearnoevil.com:monitoring/nmsui.git
or:
git remote set-url origin https://gitlab01.iwillfearnoevil.com/monitoring/nmsui.git
</pre>

== Sort git branches by last commit ==
<pre>
git for-each-ref --sort=committerdate refs/heads/ --format='%(HEAD) %(color:yellow)%(refname:short)%(color:reset) - %(color:red)%(objectname:short)%(color:reset) - %(contents:subject) - %(authorname) (%(color:green)%(committerdate:relative)%(color:reset))'
</pre>

== Show git commit hashes for each branch sorted by date ==
<pre>
git for-each-ref --sort=-committerdate refs/heads/
</pre>

== Do a git diff between two branches ==
<pre>
git diff <branch>..origin/<branch2>
</pre>

== Update Submodules ==
[https://stackoverflow.com/questions/1030169/pull-latest-changes-for-all-git-submodules pull-latest-changes-for-all-git-submodules]
<pre>
git submodule update --recursive --init
git submodule update --recursive --remote
</pre>
== Checkout specific commit hash ==
This can be useful when the hash is not in the expected branch, or when you are in a detached head state
<pre>
git clone [remote_address_here] my_repo
cd my_repo
git reset --hard [ENTER HERE THE COMMIT HASH YOU WANT]
</pre>

== Change submodule URL ==
This is very much a hammer way of doing this. I have seen elegant ways online, but they seem inconsistent when something goes wrong. This way is reproducible as far as I am concerned.

This example assumes that directory/ is where your submodule lives.
<pre>
edit .gitmodule
change values
rm -rf directory/
git submodule update --init --recursive --remote
cd into directory/
git pull whatever submodule branch you need
cd ..
git add -A
git commit -m 'save new submodule changes'
git push
</pre>

== Save image in README.md on github.com ==
Per: [https://stackoverflow.com/questions/14494747/how-to-add-images-to-readme-md-on-github Stackoverflow]

Works:
<pre>

Very Simple : Can be done using Ctrl + C/V

Most of the answers here directly or indirectly involve uploading the image somewhere else & then providing a link to it.

It can be done very simply by just copying any image and pasting it while editing Readme.md

Copying the image - You can just click on the image file and use Ctrl + C or may copy the screenshot image to your clipboard using the snipping tool
You can then simply do Ctrl + V while editing Readme.md

Guithub will automatically upload it to user-images.githubusercontent.com and a link to it will be inserted there
</pre>

== Change remote URL ==
[https://stackoverflow.com/questions/2432764/how-do-i-change-the-uri-url-for-a-remote-git-repository Change Git remote URL]
<pre>
git remote set-url origin new.git.url/here
</pre>

== Ignore local changes and pull ==
When you simply want to start over and nuke all your local mess....

[https://stackoverflow.com/questions/4157189/how-to-git-pull-while-ignoring-local-changes Stackoverflow on doing this]

<pre>
git fetch --all
git reset --hard origin/<branch_name>
</pre>

== Clean up local orphan branches ==
When you have branches locally present that have been merged on the remote server this works to clean out your local repo.

If there are still some that you want to keep, add in a 'grep -v "someName"' before xargs

[https://stackoverflow.com/questions/7726949/remove-tracking-branches-no-longer-on-remote remove tracking branches no longer on remote]
<pre>
git branch --merged | grep -v "\*" | xargs -n 1 git branch -d
</pre>

== Get latest commit hash for scripts ==
<pre>
git rev-parse HEAD
</pre>

== Remove submodules of submodules ==
* This has been a recurring painful process. This is one solution that has worked for me
[https://stackoverflow.com/questions/4185365/no-submodule-mapping-found-in-gitmodule-for-a-path-thats-not-a-submodule Stack Overflow discussion on this]
<pre>
git clone --recursive REPO
cd down/to/submodule
git checkout branch needed
rm -f .gitmodules
git submodule -init
git submodule -update
git rm --cached file/path/with/error/message
git status
git add -A
git commit -m 'Remove submodules of submodules'
git push
cd parent repository
cat .gitmodules
Verify no references to submodules of submodules
git submodule status --recursive
git status
git add -A
git commit -m 'Remove references in submodule to nested submodules'
git push
</pre>

== Nuke a bad commit before push ==
* When you have saved a commit but it is not been pushed and you want to get rid of it
<pre>
Keep the work done:
git reset --soft HEAD~1

Destroy all work done and get last full commit
git reset --hard HEAD~1
</pre>

== Drop changes and pull submodules ==
* When you have a submodule that is out of whack with the main repo and you cannot figure out which commit it is supposed to be at easily
<pre>
cd path/to/submodule
git reset --hard
git clean -fdx
cd ..
git submodule update --init --recursive --force
</pre>

== mis-synced between parent and submodule commits ==
<pre>
git submodule update --init --recursive --force
</pre>

[[Category:Git]]

Git notes

2025-05-02T17:59:07Z

Chubbard:

Main Page

2025-04-21T18:31:49Z

Chubbard:

Fault Management notes, thoughts, and example code

My code is finally up on github.com! Right now I am squishing bugs that I am finding which are not critical issues, but look bad. Additionally I have begun the ECE or event correlation engine work for the different dashboards. I still have not gotten to an installer, or added the seed data for the database. I will be focusing on that soon. I want to get a little more functionality in place to prove the tool is useful before focusing on this part. Additionally I have had some thoughts on how to templatize some of the checks better. I am still kinda kicking the idea around right now to see what kind of weak points I will run into, but the basic idea is along the lines of give me a SNMP table, and if return data is numbers graph/save it. If args are passed for what we care about, parse and event on what is found all at the template level. I think that I can build a simple skeleton for this where just about anything can be added for the check by doing this. String compare, existence, or number comparison. I kinda like this idea, since we can get a lot of data, but 99% of the time we only care about specific parts of a given table. I would like to make this something that can be created from the web page if possible, but I am not sure how ugly that would get..

I am mainly updating this wiki for the NMS work I am doing, and attempting to get some more exposure in the greater world for assistance on code. I am not a developer, just someone who is frustrated at a lack of good tools. I will be adding the NMS up to gitgub in the future as two separate repos. The first one will be the API server. The second is the front-end UI portion.

The backend is written in PHP, and has recently been migrated from PHP7.4 to 8.1. I used the Slim4 skeleton as the base of the application.

The frontend is using bootstrap, PHP and javascript.

 Update status: 04-21-25
* Still running 2X2 on server. Load Average consistent 1.10 15 min, and peaks 3.50 on 1min when iteration starts up
* Not been focused on this too much lately. Continuing to try to get Devices to look the way they do in my head...
* Still eating Crayons...

 Update status: 03-26-25
* Still working on UX, as I know I do not make pretty pages
* Looks a little better on the branch I am working on, but still not amazing or great... Sigh...
* I should stick to eating Crayons instead of trying to use them to make something good looking...

 Update status: 01-02-25
* Currently working on getting the UI to not look like a first time high school project
* Minor bug squishing
* Investigating template rework to better calculate percentages to store in metrics
* Beginning to investigate storing metric data in influxdb

 Update status: 10-30-24
* Currently hitting performance limits with 2 core/2G RAM with 61 servers. Server getting laggy..
* Will encourage setups as < 50 servers, 2x2 is fine.
* > 51 and < 125 testing as 4x2. This is not a RAM intense app, it is more thready and core bound. Then Drive IO is going to become the bottleneck before RAM

 Update status: 10-15-24
* Purchased ChartJS license (I may hate JS, but it makes pretty pics)

 Update status: 10-08-24
* Minor bug squishing
* Testing using evil Javascript to make pretty graphs
* Going to license CanvasJS for this (even a n00b can figure it out)

 Update status: 08-18-24
* Some minor bug fixes, but not too much coding done
* beginning focus on ECE and seeing what a mess it is. Not happy about it. Will likely redesign this since it was a skel anyway. This needs to be simple and understandable dammit.
* Looking at changing frontend UI for main page.
* Building more templates for standards
* Dblchecking my logic for NRPE or shell commands. This could use more work. NRPE failing for something not the command itself should have discrete alarm values. Need to think about this more..
* SNMP checks need to be smarter, and need parsible thresholds on a per host basis, not a per check basis

 Update status: 04-16-24
* Still squishy-squishy on bugs
* Focusing on stability and clean UI

Current Loads: 47 devices
* 2 core 2 GB RAM
* Load averages consistent: 0.70,1.5,1.6
* From this with a average number of checks ( ~10 per host ) reliable monitoring can be done on 50 devices with decent results and minimal hardware for small environments.

 Update on status: 03-21-2024 
* Many Many squishes of bugs
* Reporting engine and templates much more usable
* More documentation links for application with base examples written
* Initial Event Correlation Engine (ECE) rules written
* Some pages written for ECE
* Did I mention squishing bugs?

 Update on status: 02-11-2024 
* Upgraded API to use PHP 8.2, not 8.1.
* GUI code was sanitized and released to github
* [https://github.com/Guyverix/Vigilare-NMS-GUI Vigilare GUI]
* [https://github.com/Guyverix/Vigilare-NMS-API Vigilare API]
* [https://github.com/Guyverix/Vigilare-NMS-POLLER Vigilare Pollers]

The focus of this wiki is for notes and gotchas for things relating to technology. It is mainly focused on the fault management tool that I am writing. However oddball things I have found or commonly forget since I rarely use them are also present. I do not go too in depth on the notes or what I find. In general it is more a quick summary and if reasonable an example that shows what the result is. I suspect that as time passes it will become more focused on the tool that I am writing. However there will likely be oddball stuff thrown in here as well that does not have to do with fault management at all....

Overall idea

I believe that fault management is commonly very much overlooked in many companies and more of a bolt-on after they have been embarrassed by an outage or network event that a customer noticed. This feels backwards to me. Fault management really should be one of the first things put into place and grow with your software and network over time. Being able to tell at a glance the "truth" of your network is almost as critical as the network running itself! Customer monitoring isn't monitoring and simply makes you look bad. If a customer or end user has to tell you something is down, you have failed. If it happens more than once then that should set off all kinds of warnings to technicians. This should not happen. The problem happens in my opinion due to management seeing monitoring as a time suck that does not really return money on their investment of time. The only time they care is when they get embarrassed, and after that they loose interest until they are embarrassed again. This is stupid. A company or technician must be able to say with confidence that their applications are working AND PROVE IT. If you cant prove anything it is just a hope that things are working as designed. Until you know, you simply are guessing on the health and availability of an application.

I have noticed a trend recently that companies are going to what I call "implied" monitoring. More looking at metrics than active validation of an application or service. While this does somewhat fit the bill for monitoring it does a major dis-service to the technicians that need to support the application. Usually this kind of monitoring will cry when something is down but does not tell you what, or where the issue happens. Only that it is happening. It is also usually slower to report the failures. That means more time for a customer to find issues than the technician. Parsing logs, and dropping them in search indexes, or forwarding matches as events are all very useful, but they are not really watching for specific application issues at the host level. I believe that an organization must work up to this kind of monitoring. It must be bult on the basics. If you do not have the basics in place, then the advanced monitoring is much less useful for an org.


My common way to approach monitoring is to start as fundamental as possible. On Linux based systems this is the way that has given me the best results.

* daemon
** dead daemon, well dead app :)
* port
** zombie processes holding a port, or different daemons attempting to use the same port is bad
* challenge and response.
** Verify which app has control of the port (did you get an http, email, ssh header? Did it respond at all?)
* performance
** This is the n+1 point. After confirming the application is running, NOW is the time to verify it is at a basic level performant.
* log and event parsing
** In depth log parsing, and event correlations. Without the above this is much less useful to a technician.
*** I have seen in RARE cases this done well, where it states what failed and which host. However that is much less likely to happen as the logs parsed do not always state which application is at fault, only that there is one present.


How you get the answers is less important than getting the answers, with one gotcha. There is no reason to degrade the host with a complex and slow service check. If the validation cannot be done cheaply, then it is likely something that should be broken down into more basic pieces. Killing your servers or pod with service checks is exactly backwards of what you need. Simple, fast, and accurate are what you need to focus on. Inaccurate data is WORSE than no data. Another useful trick is to assume failure until the application proves that it is doing what you expect. I always try to avoid a bias of assuming something works until it can prove it in a service check. Until that time, it is only your opinion that things are working.

Historically this has been with SNMP and NRPE service checks on a common 5 minute iteration cycle. This allows for scale of many checks across a fleet of hosts within the cycle, as well as a decent starting point for monitoring hosts. I realize that some technicians prefer a faster cycle time, but at the end of the day, you are still talking about human response times and investigation times. Getting things like sub-minute reporting does no good at all to a technician who is troubleshooting an issue. Additionally, fast cycle times do not really lend themselves well to the concept of retry. You will loose packets from time to time, your application will do weird crap from time to time. That's just fact. Make sure your monitoring does not loose its mind and scream the world is burning due to a transient packet-loss issue. Using something like retry will make it more likely you are picking up a legitimate event and not a transient failure.

Every time there is an "Outage" or severe service degrade, one of the first things that should be brought up is did the monitoring catch this issue? Was it actually granular enough to state what the issue was, or just a side affect of the issue? Getting an alarm for webiste down, vs. database down are two very different things. Both will imply a 100% outage, but usually one is faster to repair than the other. Also looking at the wrong things; say the webserver when the database is toast simply slows down the response for getting a database back online.

== History of this wiki ==
MediaWiki destination for random notes and examples I do not want to loose or forget.
* The site is for myself and friends who commonly use bash and other utilities and can have a one-stop-shop to find that oddball thing that was found six months ago and vaguely remembered. The site overall is not for the general public, however if you make it in here feel free to browse.
* Keep in mind however I do have security measures in place and poking hard at stuff will block you from the domain for 30 days + if you hammer really hard.
* '''If someone wants access to actually ADD information in here please sign up for an account, and I will likely grant access.''' I do try to set everything into some kind of category for easier searches as well as an attempt to keep this somewhat organized. Whenever possible (or I remember to do it) I do try to link to the original sources of the information. They are usually from SE, or other Q&A sites, so some of the comments are useful as well.
* This is not wikipedia, likely the site is not going to be polished, since it is more of a catchall wiki on doing different things. There is not going to be too much rhyme or reason on what is posted on this wiki. Overall if it is something that I have had to do more than once and look up every time, I will have notes on it in here so I will not have to search next time.
* There will be times that there are references to personal servers on my network, or oddball hostnames that are tied to iwillfearnoevil.com. It is unlikely that access will be granted to those hosts unless there is a very specific reason to do so. So dont bother asking :P
* '''Category NMS has notes on my progress of my NMS design, and thoughts on monitoring overall''' [https://wiki.iwillfearnoevil.com/mediawiki/index.php/Category:NMS Category NMS]

== Misc Notes on using Mediawiki ==
Consult the [https://www.mediawiki.org/wiki/Special:MyLanguage/Help:Contents User's Guide] for information on using the wiki software.

== Getting started ==
* [https://www.mediawiki.org/wiki/Special:MyLanguage/Manual:Configuration_settings Configuration settings list]
* [https://www.mediawiki.org/wiki/Special:MyLanguage/Manual:FAQ MediaWiki FAQ]
* [https://lists.wikimedia.org/postorius/lists/mediawiki-announce.lists.wikimedia.org/ MediaWiki release mailing list]
* [https://www.mediawiki.org/wiki/Special:MyLanguage/Localisation#Translation_resources Localise MediaWiki for your language]
* [https://www.mediawiki.org/wiki/Special:MyLanguage/Manual:Combating_spam Learn how to combat spam on your wiki]
[[Category:General]]

Category:Ssh

2025-04-15T20:27:11Z

Chubbard: Created page with "Generic "anything" related specifically to ssh and ssh services"

Generic "anything" related specifically to ssh and ssh services

Ssh

2025-04-15T17:37:06Z

Chubbard: Created page with "== SSH examples for abnormal tasks == Yes, I can never remember exactly how to get the !#$!@# port forwarding done correctly. Bah! ===SSH port forwarding for postgres=== * Accounting for possibility of using abnormal ssh ports and strange ssh keys. * Connect pgadmin to localhost:5432 and set your authentication <pre> export KEY=~/.ssh/some_key export PORT=2345 export JUMPBOX=192.168.15.58 ssh -i ${KEY} -p ${PORT} -L 5432:192.168.15.250:5432 SSH_USER@$${JUMPBOX} -N </p..."

== SSH examples for abnormal tasks ==
Yes, I can never remember exactly how to get the !#$!@# port forwarding done correctly. Bah!

===SSH port forwarding for postgres===
* Accounting for possibility of using abnormal ssh ports and strange ssh keys.
* Connect pgadmin to localhost:5432 and set your authentication

<pre>
export KEY=~/.ssh/some_key
export PORT=2345
export JUMPBOX=192.168.15.58
ssh -i ${KEY} -p ${PORT} -L 5432:192.168.15.250:5432 SSH_USER@$${JUMPBOX} -N
</pre>

[[Category:Bash]][[Category:Ssh]]

Bash-ps-examples

2025-04-10T16:35:10Z

Chubbard:

== Generic Page of PS options ==

=== threads from process ===
See the threads for a given process
* [https://serverfault.com/questions/932406/how-to-tell-threads-from-processes-in-top-and-ps-on-linux show threads from process]
<pre>
ps aux |grep processName
ps -fly -T -p PID
</pre>

[[Category:Bash]]

Bash-ps-examples

2025-04-10T16:34:59Z

Chubbard: Created page with "== Generic Page of PS options == === threads from process === See the threads for a given process * [https://serverfault.com/questions/932406/how-to-tell-threads-from-processes-in-top-and-ps-on-linux show threads from process] <pre> ps aux |grep processName ps -fly -T -p PID </pre>"

Postgres

2025-04-10T16:29:58Z

Chubbard:

== Postgres Notes ==
I generally use MySQL, however there have been cases where I need to use Postgres. I can never remember the exact syntax to do basic things, so here we are...

=== Create User and Database ===
Admin login to Postgeres (fresh install Pg14)
<pre>
sudo -u postgres psql postgres
</pre>
Create User
<pre>
CREATE ROLE someUser LOGIN PASSWORD 'somePassword';
</pre>
Create database and add someUser as the owner
<pre>
CREATE DATABASE databaseName with owner = someUser;
</pre>
Validate that this worked
<pre>
psql -h localhost -d databaseName -U someUser -p 5432
Password for user someUser: somePassword
psql (14.11 (Ubuntu 14.11-0ubuntu0.22.04.1))
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits: 256, compression: off)
Type "help" for help.
</pre>

=== Useful commands to remember ===
* \q quit
* \dt display tables
* \l list databases

=== Migrate from MySQL to Psql ===
install pgloader application

Edit script.lisp
<pre>
/* content of the script.lisp */
LOAD DATABASE
FROM mysql://mysqlUser:mysqlPassword@localhost|IP/oldDatabaseName
INTO postgresql://someUser:somePassword@localhost/databaseName;
</pre>
Run: pgloader script.lisp
<pre>
2024-04-03T18:56:34.028000Z LOG pgloader version "3.6.3~devel"
2024-04-03T18:56:34.036000Z LOG Data errors in '/tmp/pgloader/'
2024-04-03T18:56:34.036000Z LOG Parsing commands from file #P"/home/chubbard/script.lisp"
2024-04-03T18:56:34.268005Z LOG Migrating from #<MYSQL-CONNECTION mysql://mysqlUser@192.168.15.250:3306/oldDatabaseName {10080B3B03}>
2024-04-03T18:56:34.272005Z LOG Migrating into #<PGSQL-CONNECTION pgsql://someUser@localhost:5432/databaseName {10080B4783}>
2024-04-03T18:56:36.244042Z LOG report summary reset
table name errors rows bytes total time
----------------------- --------- --------- --------- --------------
fetch meta data 0 3 1.204s
Create Schemas 0 0 0.024s
Create SQL Types 0 0 0.024s
Create tables 0 2 0.076s
Set Table OIDs 0 1 0.020s
----------------------- --------- --------- --------- --------------
databaseName.state 0 8 0.7 kB 0.084s
----------------------- --------- --------- --------- --------------
COPY Threads Completion 0 4 0.092s
Index Build Completion 0 2 0.080s
Create Indexes 0 2 0.016s
Reset Sequences 0 1 0.092s
Primary Keys 0 1 0.004s
Create Foreign Keys 0 0 0.000s
Create Triggers 0 0 0.000s
Set Search Path 0 1 0.000s
Install Comments 0 0 0.000s
----------------------- --------- --------- --------- --------------
Total import time ✓ 8 0.7 kB 0.284s

</pre>

=== Dump a Postgres database ===
To dump just the schema and indexes
<pre>
pg_dump -s databaseName -U someUser -h localhost > databaseName_database_schema_postgres.sql
</pre>

=== Find active queries ===
See what is actually happening at the time of a query
[https://stackoverflow.com/questions/27435839/how-to-list-active-connections-on-postgresql List active connections on postgresql]
<pre>
someDb=# SELECT
someDb-# pid
someDb-# ,datname
someDb-# ,usename
someDb-# ,application_name
someDb-# ,client_hostname
someDb-# ,client_port
someDb-# ,backend_start
someDb-# ,query_start
someDb-# ,query
someDb-# ,state
someDb-# FROM pg_stat_activity
someDb-# WHERE state = 'active';
pid | datname | usename | application_name | client_hostname | client_port | backend_start | query_start | query | state
------+---------+---------+------------------+-----------------+-------------+-------------------------------+-------------------------------+-----------------------------------------+--------
9132 | someDb | someDb | psql | | -1 | 2025-04-10 16:23:07.854726+00 | 2025-04-10 16:25:08.334369+00 | SELECT +| active
| | | | | | | | pid +|
| | | | | | | | ,datname +|
| | | | | | | | ,usename +|
| | | | | | | | ,application_name +|
| | | | | | | | ,client_hostname +|
| | | | | | | | ,client_port +|
| | | | | | | | ,backend_start +|
| | | | | | | | ,query_start +|
| | | | | | | | ,query +|
| | | | | | | | ,state +|
| | | | | | | | FROM pg_stat_activity +|
| | | | | | | | WHERE state = 'active'; |
5416 | | someDb | walreceiver | | 57680 | 2025-04-10 15:38:21.861295+00 | 2025-04-10 15:38:21.874055+00 | START_REPLICATION A/E2000000 TIMELINE 1 | active
</pre>

=== Links ===
[https://stackoverflow.com/questions/2172569/how-to-login-and-authenticate-to-postgresql-after-a-fresh-install| Stack Overflow fresh install notes]

[https://serverfault.com/questions/198002/postgresql-what-does-grant-all-privileges-on-database-do| Server Fault Grants and permissions information]

[[Category:Postgres]]

Postgres

2025-04-10T16:28:58Z

Chubbard: /* Dump a Postgres database */

== Postgres Notes ==
I generally use MySQL, however there have been cases where I need to use Postgres. I can never remember the exact syntax to do basic things, so here we are...

=== Create User and Database ===
Admin login to Postgeres (fresh install Pg14)
<pre>
sudo -u postgres psql postgres
</pre>
Create User
<pre>
CREATE ROLE someUser LOGIN PASSWORD 'somePassword';
</pre>
Create database and add someUser as the owner
<pre>
CREATE DATABASE databaseName with owner = someUser;
</pre>
Validate that this worked
<pre>
psql -h localhost -d databaseName -U someUser -p 5432
Password for user someUser: somePassword
psql (14.11 (Ubuntu 14.11-0ubuntu0.22.04.1))
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits: 256, compression: off)
Type "help" for help.
</pre>

=== Useful commands to remember ===
* \q quit
* \dt display tables
* \l list databases

=== Migrate from MySQL to Psql ===
install pgloader application

Edit script.lisp
<pre>
/* content of the script.lisp */
LOAD DATABASE
FROM mysql://mysqlUser:mysqlPassword@localhost|IP/oldDatabaseName
INTO postgresql://someUser:somePassword@localhost/databaseName;
</pre>
Run: pgloader script.lisp
<pre>
2024-04-03T18:56:34.028000Z LOG pgloader version "3.6.3~devel"
2024-04-03T18:56:34.036000Z LOG Data errors in '/tmp/pgloader/'
2024-04-03T18:56:34.036000Z LOG Parsing commands from file #P"/home/chubbard/script.lisp"
2024-04-03T18:56:34.268005Z LOG Migrating from #<MYSQL-CONNECTION mysql://mysqlUser@192.168.15.250:3306/oldDatabaseName {10080B3B03}>
2024-04-03T18:56:34.272005Z LOG Migrating into #<PGSQL-CONNECTION pgsql://someUser@localhost:5432/databaseName {10080B4783}>
2024-04-03T18:56:36.244042Z LOG report summary reset
table name errors rows bytes total time
----------------------- --------- --------- --------- --------------
fetch meta data 0 3 1.204s
Create Schemas 0 0 0.024s
Create SQL Types 0 0 0.024s
Create tables 0 2 0.076s
Set Table OIDs 0 1 0.020s
----------------------- --------- --------- --------- --------------
databaseName.state 0 8 0.7 kB 0.084s
----------------------- --------- --------- --------- --------------
COPY Threads Completion 0 4 0.092s
Index Build Completion 0 2 0.080s
Create Indexes 0 2 0.016s
Reset Sequences 0 1 0.092s
Primary Keys 0 1 0.004s
Create Foreign Keys 0 0 0.000s
Create Triggers 0 0 0.000s
Set Search Path 0 1 0.000s
Install Comments 0 0 0.000s
----------------------- --------- --------- --------- --------------
Total import time ✓ 8 0.7 kB 0.284s

</pre>

=== Dump a Postgres database ===
To dump just the schema and indexes
<pre>
pg_dump -s databaseName -U someUser -h localhost > databaseName_database_schema_postgres.sql
</pre>

=== Find active queries ===
See what is actually happening at the time of a query

<pre>
someDb=# SELECT
someDb-# pid
someDb-# ,datname
someDb-# ,usename
someDb-# ,application_name
someDb-# ,client_hostname
someDb-# ,client_port
someDb-# ,backend_start
someDb-# ,query_start
someDb-# ,query
someDb-# ,state
someDb-# FROM pg_stat_activity
someDb-# WHERE state = 'active';
pid | datname | usename | application_name | client_hostname | client_port | backend_start | query_start | query | state
------+---------+---------+------------------+-----------------+-------------+-------------------------------+-------------------------------+-----------------------------------------+--------
9132 | someDb | someDb | psql | | -1 | 2025-04-10 16:23:07.854726+00 | 2025-04-10 16:25:08.334369+00 | SELECT +| active
| | | | | | | | pid +|
| | | | | | | | ,datname +|
| | | | | | | | ,usename +|
| | | | | | | | ,application_name +|
| | | | | | | | ,client_hostname +|
| | | | | | | | ,client_port +|
| | | | | | | | ,backend_start +|
| | | | | | | | ,query_start +|
| | | | | | | | ,query +|
| | | | | | | | ,state +|
| | | | | | | | FROM pg_stat_activity +|
| | | | | | | | WHERE state = 'active'; |
5416 | | someDb | walreceiver | | 57680 | 2025-04-10 15:38:21.861295+00 | 2025-04-10 15:38:21.874055+00 | START_REPLICATION A/E2000000 TIMELINE 1 | active
</pre>

=== Links ===
[https://stackoverflow.com/questions/2172569/how-to-login-and-authenticate-to-postgresql-after-a-fresh-install| Stack Overflow fresh install notes]

[https://serverfault.com/questions/198002/postgresql-what-does-grant-all-privileges-on-database-do| Server Fault Grants and permissions information]

[[Category:Postgres]]